MySQL
WIJZIG LIJST

Syntaxis

• ALTER [IGNORE] TABLE tbl_name [ alter_specification [, alter_specification] ...] [partitie_opties]

Opmerkingen

    alter_specification: table_options
      | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
      | ADD [COLUMN] (col_name column_definition,...)
      | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
      | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
      | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
      | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
      | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
      | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
      | DROP [COLUMN] col_name
      | DROP PRIMARY KEY
      | DROP {INDEX|KEY} index_name
      | DROP FOREIGN KEY fk_symbol
      | DISABLE KEYS
      | ENABLE KEYS
      | RENAME [TO|AS] new_tbl_name
      | RENAME {INDEX|KEY} old_index_name TO new_index_name
      | ORDER BY col_name [, col_name] ...
      | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
      | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
      | DISCARD TABLESPACE
      | IMPORT TABLESPACE
      | FORCE
      | {WITHOUT|WITH} VALIDATION
      | ADD PARTITION (partition_definition)
      | DROP PARTITION partition_names
      | DISCARD PARTITION {partition_names | ALL} TABLESPACE
      | IMPORT PARTITION {partition_names | ALL} TABLESPACE
      | TRUNCATE PARTITION {partition_names | ALL}
      | COALESCE PARTITION number
      | REORGANIZE PARTITION partition_names INTO (partition_definitions)
      | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
      | ANALYZE PARTITION {partition_names | ALL}
      | CHECK PARTITION {partition_names | ALL}
      | OPTIMIZE PARTITION {partition_names | ALL}
      | REBUILD PARTITION {partition_names | ALL}
      | REPAIR PARTITION {partition_names | ALL}
      | REMOVE PARTITIONING
      | UPGRADE PARTITIONING
   index_col_name: col_name [(length)] [ASC | DESC]
   index_type: USING {BTREE | HASH}
   index_option: KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'

Veranderende opslagmotor; herbouw tafel; wijzig file_per_table

Als t1 momenteel bijvoorbeeld geen InnoDB-tabel is, wijzigt deze verklaring de opslag-engine in InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Als de tabel al InnoDB is, zal dit de tabel en zijn indexen opnieuw opbouwen en een effect hebben vergelijkbaar met OPTIMIZE TABLE . U kunt enige verbetering van de schijfruimte krijgen.

Als de waarde van innodb_file_per_table momenteel anders is dan de waarde die van kracht was toen t1 werd gebouwd, wordt dit geconverteerd naar (of van) file_per_table.

ALTER KOLOM VAN LIJST

CREATE DATABASE stackoverflow;

USE stackoverflow;

Create table stack(
    id_user int NOT NULL,
    username varchar(30) NOT NULL,
    password varchar(30) NOT NULL
);

ALTER TABLE stack ADD COLUMN submit date NOT NULL; -- add new column
ALTER TABLE stack DROP COLUMN submit; -- drop column
ALTER TABLE stack MODIFY submit DATETIME NOT NULL; -- modify type column
ALTER TABLE stack CHANGE submit submit_date DATETIME NOT NULL; -- change type and name of column
ALTER TABLE stack ADD COLUMN mod_id INT NOT NULL AFTER id_user; -- add new column after existing column

ALTER-tabel voeg INDEX toe

Om de prestaties te verbeteren, zou je misschien indexen aan kolommen willen toevoegen

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`column_name`)

wijzigen om samengestelde (meerdere kolommen) indexen toe te voegen

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`col1`,`col2`)

Wijzig de waarde voor automatisch verhogen

Het wijzigen van een waarde voor automatisch verhogen is handig als u na een massieve verwijdering geen opening in een kolom AUTO_INCREMENT wilt.

U hebt bijvoorbeeld veel ongewenste (advertentie) rijen in uw tabel geplaatst, u hebt ze verwijderd en u wilt de kloof in waarden voor automatisch ophogen corrigeren. Stel dat de MAX-waarde van de kolom AUTO_INCREMENT nu 100 is. U kunt het volgende gebruiken om de waarde voor automatisch verhogen vast te stellen.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Het type van een primaire-sleutelkolom wijzigen

ALTER TABLE fish_data.fish DROP PRIMARY KEY;
ALTER TABLE fish_data.fish MODIFY COLUMN fish_id DECIMAL(20,0) NOT NULL PRIMARY KEY;

Een poging om het type van deze kolom te wijzigen zonder eerst de primaire sleutel te verwijderen, zou een fout veroorzaken.

Wijzig kolomdefinitie

Om de definitie van een DB-kolom te wijzigen, kan de onderstaande query bijvoorbeeld worden gebruikt als we dit DB-schema hebben

users (
    firstname varchar(20),
    lastname varchar(20),
    age char(2)
)

Om het type age te wijzigen van char naar int , gebruiken we de onderstaande zoekopdracht:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Algemeen formaat is:

ALTER TABLE table_name CHANGE column_name new_column_definition

Hernoemen van een MySQL-database

Er is geen enkele opdracht om de naam van een MySQL-database te wijzigen, maar een eenvoudige oplossing kan worden gebruikt om een back-up te maken en te herstellen:

mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>

Stappen:

1. Kopieer de bovenstaande regels naar een teksteditor.
2. Vervang alle verwijzingen naar <old name> , <new name> en <password> (+ optioneel root om een andere gebruiker te gebruiken) door de relevante waarden.
3. Voer een voor een uit op de opdrachtregel (ervan uitgaande dat de map "bin" van MySQL zich in het pad bevindt en voer "y" in wanneer daarom wordt gevraagd).

Alternatieve stappen:

Hernoem (verplaats) elke tabel van de ene DB naar de andere. Doe dit voor elke tafel:

RENAME TABLE `<old db>`.`<name>` TO  `<new db>`.`<name>`;

Je kunt die uitspraken maken door zoiets te doen

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
                           'new_db.', table_name)
    FROM information_schema.TABLES
    WHERE table_schema = 'old_db';

Waarschuwing. Probeer geen enkele vorm van tabel of database te maken door eenvoudig bestanden op het bestandssysteem te verplaatsen. Dit werkte prima in de oude dagen van alleen MyISAM, maar in de nieuwe dagen van InnoDB en tablespaces, zal het niet werken. Vooral wanneer het "Data Dictionary" van het bestandssysteem naar systeem InnoDB-tabellen wordt verplaatst, waarschijnlijk in de volgende grote release. Het verplaatsen (in tegenstelling tot alleen DROPping ) van een PARTITION van een InnoDB-tabel vereist het gebruik van "transporteerbare tabelruimten". In de nabije toekomst zal er zelfs geen bestand zijn om naar te reiken.

De namen van twee MySQL-databases omwisselen

De volgende opdrachten kunnen worden gebruikt om de namen van twee MySQL-databases ( <db1> en <db2> ) te verwisselen:

mysqladmin -uroot -p<password> create swaptemp
mysqldump -uroot -p<password> --routines <db1> | mysql -uroot -p<password> swaptemp
mysqladmin -uroot -p<password> drop <db1>
mysqladmin -uroot -p<password> create <db1>
mysqldump -uroot -p<password> --routines <db2> | mysql -uroot -p<password> <db1>
mysqladmin -uroot -p<password> drop <db2>
mysqladmin -uroot -p<password> create <db2>
mysqldump -uroot -p<password> --routines swaptemp | mysql -uroot -p<password> <db2>
mysqladmin -uroot -p<password> drop swaptemp

Stappen:

1. Kopieer de bovenstaande regels naar een teksteditor.
2. Vervang alle verwijzingen naar <db1> , <db2> en <password> (+ optioneel root om een andere gebruiker te gebruiken) door de relevante waarden.
3. Voer een voor een uit op de opdrachtregel (ervan uitgaande dat de map "bin" van MySQL zich in het pad bevindt en voer "y" in wanneer daarom wordt gevraagd).

Een MySQL-tabel hernoemen

U kunt een tabel hernoemen met één opdracht:

RENAME TABLE `<old name>` TO `<new name>`;

De volgende syntaxis doet precies hetzelfde:

ALTER TABLE `<old name>` RENAME TO `<new name>`;

Als u de naam van een tijdelijke tabel ALTER TABLE , moet de ALTER TABLE versie van de syntaxis worden gebruikt.

Stappen:

1. Vervang <old name> en <new name> in de bovenstaande regel door de relevante waarden. Opmerking: als de tabel naar een andere database wordt verplaatst, de dbname . syntaxis van de tablename kan worden gebruikt voor <old name> en / of <new name> .
2. Voer het uit op de relevante database in de MySQL-opdrachtregel of een client zoals MySQL Workbench. Opmerking: de gebruiker moet ALTER- en DROP-rechten hebben voor de oude tabel en CREATE en INSERT voor de nieuwe.

Naam van een kolom in een MySQL-tabel wijzigen

Het hernoemen van een kolom kan in een enkele instructie worden gedaan, maar naast de nieuwe naam moet ook de "kolomdefinitie" (dwz het gegevenstype en andere optionele eigenschappen zoals nullabiliteit, automatische incrementering enz.) Worden opgegeven.

ALTER TABLE `<table name>` CHANGE `<old name>` `<new name>` <column definition>;

Stappen:

1. Open de MySQL-opdrachtregel of een client zoals MySQL Workbench.
2. Voer de volgende instructie uit: SHOW CREATE TABLE <table name>; (vervangt <table name> door de relevante waarde).
3. Noteer de volledige kolomdefinitie voor de kolom waarvan u de naam wilt wijzigen (dwz alles wat wordt weergegeven na de naam van de kolom maar vóór de komma die het scheidt van de volgende kolomnaam) .
4. Vervang <old name> , <new name> en <column definition> in de bovenstaande regel door de relevante waarden en voer deze vervolgens uit.