MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL ALTER TABLE

From WikiOD

Syntax[edit | edit source]

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

Remarks[edit | edit source]

    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'

table_options: table_option [[,] table_option] ... (see CREATE TABLE options)

partition_options: (see CREATE TABLE options)

Ref: MySQL 5.7 Reference Manual / ... / ALTER TABLE Syntax / 14.1.8 ALTER TABLE Syntax

Changing storage engine; rebuild table; change file_per_table[edit | edit source]

For example, if t1 is currently not an InnoDB table, this statement changes its storage engine to InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

If the table is already InnoDB, this will rebuild the table and its indexes and have an effect similar to OPTIMIZE TABLE. You may gain some disk space improvement.

If the value of innodb_file_per_table is currently different than the value in effect when t1 was built, this will convert to (or from) file_per_table.

ALTER COLUMN OF TABLE[edit | edit source]

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

Renaming a MySQL table[edit | edit source]

Renaming a table can be done in a single command:

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

The following syntax does exactly the same:

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

If renaming a temporary table, the ALTER TABLE version of the syntax must be used.

Steps:

  1. Replace <old name> and <new name> in the line above with the relevant values. Note: If the table is being moved to a different database, the dbname.tablename syntax can be used for <old name> and/or <new name>.
  2. Execute it on the relevant database in the MySQL command line or a client such as MySQL Workbench. Note: The user must have ALTER and DROP privileges on the old table and CREATE and INSERT on the new one.

Change auto-increment value[edit | edit source]

Changing an auto-increment value is useful when you don't want a gap in an AUTO_INCREMENT column after a massive deletion.

For example, you got a lot of unwanted (advertisement) rows posted in your table, you deleted them, and you want to fix the gap in auto-increment values. Assume the MAX value of AUTO_INCREMENT column is 100 now. You can use the following to fix the auto-increment value.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Changing the type of a primary key column[edit | edit source]

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

An attempt to modify the type of this column without first dropping the primary key would result in an error.

Change column definition[edit | edit source]

The change the definition of a db column, the query below can be used for example, if we have this db schema

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

To change the type of age column from char to int, we use the query below:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

General format is:

ALTER TABLE table_name CHANGE column_name new_column_definition

Renaming a MySQL database[edit | edit source]

There is no single command to rename a MySQL database but a simple workaround can be used to achieve this by backing up and restoring:

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>

Steps:

  1. Copy the lines above into a text editor.
  2. Replace all references to <old name>, <new name> and <password> (+ optionally root to use a different user) with the relevant values.
  3. Execute one by one on the command line (assuming the MySQL "bin" folder is in the path and entering "y" when prompted).

Alternative Steps:

Rename (move) each table from one db to the other. Do this for each table:

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

You can create those statements by doing something like

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

Warning. Do not attempt to do any sort of table or database by simply moving files around on the filesystem. This worked fine in the old days of just MyISAM, but in the new days of InnoDB and tablespaces, it won't work. Especially when the "Data Dictionary" is moved from the filesystem into system InnoDB tables, probably in the next major release. Moving (as opposed to just DROPping) a PARTITION of an InnoDB table requires using "transportable tablespaces". In the near future, there won't even be a file to reach for.

Swapping the names of two MySQL databases[edit | edit source]

The following commands can be used to swap the names of two MySQL databases (<db1> and <db2>):

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

Steps:

  1. Copy the lines above into a text editor.
  2. Replace all references to <db1>, <db2> and <password> (+ optionally root to use a different user) with the relevant values.
  3. Execute one by one on the command line (assuming the MySQL "bin" folder is in the path and entering "y" when prompted).

ALTER table add INDEX[edit | edit source]

To improve performance one might want to add indexes to columns

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

altering to add composite (multiple column) indexes

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

Renaming a column in a MySQL table[edit | edit source]

Renaming a column can be done in a single statement but as well as the new name, the "column definition" (i.e. its data type and other optional properties such as nullability, auto incrementing etc.) must also be specified.

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

Steps:

  1. Open the MySQL command line or a client such as MySQL Workbench.
  2. Run the following statement: SHOW CREATE TABLE <table name>; (replacing <table name> with the relevant value).
  3. Make a note of the entire column definition for the column to be renamed (i.e. everything that appears after the name of the column but before the comma separating it from the next column name).
  4. Replace <old name>, <new name> and <column definition> in the line above with the relevant values and then execute it.

Credit:Stack_Overflow_Documentation