MySQL alter TABLE Structure

The following articles mainly describe the actual operations of modifying the alter TABLE structure in MySQL. If you are interested in the actual operations of modifying the alter TABLE structure in MySQL, you can browse the following articles, the following is a detailed description of the relevant content.

2009-12-04 13: 51 ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec...]

 
 

  
  
alter_specification:  
  
  
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]  
  
  
or ADD INDEX [index_name] (index_col_name,...)  
  
  
or ADD PRIMARY KEY (index_col_name,...)  
  
  
or ADD UNIQUE [index_name] (index_col_name,...)  
  
  
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}  
  
  
or CHANGE [COLUMN] old_col_name create_definition  
  
  
or MODIFY [COLUMN] create_definition  
  
  
or DROP [COLUMN] col_name  
  
  
or DROP PRIMARY KEY  
  
  
or DROP INDEX index_name  
  
  
or RENAME [AS] new_tbl_name  
  
  
or table_options 
 
 

Alter table allows you to modify the structure of an existing TABLE. For example, you can add or delete columns, create or delete indexes, change existing column types, or rename columns or tables. You can also change the table annotations and table types. See 7.7 create table syntax.

If you use MySQL to modify the TABLE structure alter table to modify a column description, but DESCRIBE tbl_name shows that your column is not modified, this may be one of the reasons why MySQL described in the implicit column description in 7.7.1 ignores your modifications. For example, if you try to change a VARCHAR to CHAR, MySQL will still use VARCHAR if the table package contains other variable-length columns.

Alter table works by creating a temporary copy of the original TABLE. The modification takes effect on the copy, and the original table is deleted and a new one is renamed. In this way, all the modifications are automatically redirected to the new table without any failed modifications. When alter table is being executed, the original TABLE can be read by other customers. Updating and writing tables is delayed until the new table is ready.

To use alter table, you need the select, insert, delete, update, create, and drop permissions on the TABLE.

IGNORE is an extension of MySQL to ANSI SQL92. If there are duplicate unique keys in the new TABLE, it controls how alter table works. If IGNORE is not specified, the copy is abandoned and restored to its original state. If IGNORE is specified, only the first row is used for the unique key, and the rest are deleted.

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single alter table statement. This is an extension of ANSI SQL92 by MySQL. SQL92 allows only one clause in each alter table statement.

CHANGE col_name, DROP col_name, and drop index are MySQL extensions to ANSI SQL92.

MODIFY is an extension of Oracle to MODIFY the alter table structure of MySQL.

The optional word COLUMN is purely noisy and can be omitted.

If you use alter table tbl_name rename as new_name without any other options, MySQL simply RENAME the file corresponding to the TABLE tbl_name. There is no need to create a temporary table.

The create_definition clause uses the same ADD and CHANGE syntax as create table. Note that the syntax includes the column name, not only the column type. See 7.7 create table syntax.

You can use the CHANGE old_col_name create_definition clause to rename a column. To do this, specify the old and new column names and the current type of the column. For example, to rename an INTEGER column from a to B, you can do this:

MySQL> alter table t1 CHANGE a B INTEGER;

If you want to CHANGE the column type rather than the name, even if they are the same, the CHANGE syntax still requires two column names. For example:

 
 

  
  
MySQL> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; 
 
 

However, in MySQL3.22.16a, you can also use MODIFY to change the column type rather than rename it:

 
 

  
  
MySQL> ALTER TABLE t1 MODIFY b BIGINT NOT NULL; 
 
 

If you use CHANGE or MODIFY to shorten a column, an index exists in the column section (for example, if you have an index with the first 10 characters of a VARCHAR column ), you cannot make a column shorter than the number of indexed characters.

When you use CHANGE or MODIFY to CHANGE the column type, MySQL tries to convert the data to the new type as much as possible.

AFTER MySQL3.22 or later, you can use FIRST or ADD... AFTER col_name to ADD columns at a specific position in a table row. By default, it is added to the last column.

Alter column specifies a new default value for the COLUMN or deletes the old default value. If the old default value is deleted and the column can be NULL, the new default value is NULL. If the column cannot be NULL, MySQL assigns a default value. The default value is described in the 7.7 create table syntax.

Drop index: delete an INDEX. This is an extension of ANSI SQL92 by MySQL.

If a column is discarded from a table, the column is also deleted from any index where they are components. If all the columns that make up an index are discarded, the index is also discarded.

Drop primary key discards the PRIMARY index. If such an index does not exist, it discards the first UNIQUE index in the table. (If primary key is not explicitly specified, MySQL marks the first unique key as primary key .)

Using the c api function MySQL_info (), you can find out how many records are copied, and (when IGNORE is used) How many records are deleted due to unique key values.

The foreign key, CHECK, and REFERENCES clauses do nothing. Their syntax only provides compatibility, making it easier to port code from other SQL servers and run applications that use REFERENCES to create tables. See the functions that MySQL 5.4 lacks.

Here is an example showing some alter table usage. We start with a table t1 created as follows:

 
 

  
  
MySQL> CREATE TABLE t1 (a INTEGER,b CHAR(10)); 
 
 

Rename the table from t1 to t2:

 
 

  
  
MySQL> ALTER TABLE t1 RENAME t2; 
 
 

To change column a, change column B from INTEGER to tinyint not null (with the same name), change column B from CHAR (10) To CHAR (20), and rename it at the same time, change from B to c:

 
 

  
  
MySQL> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); 
 
 

Add a new TIMESTAMP column named d:

 
 

  
  
MySQL> ALTER TABLE t2 ADD d TIMESTAMP; 
 
 

Add an index on column d and set column a as the primary key:

 
 

  
  
MySQL> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); 
 
 

Delete column c:

 
 

  
  
MySQL> ALTER TABLE t2 DROP COLUMN c; 
 
 

Add a new AUTO_INCREMENT integer column named c:

 
 

  
  
MySQL> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,  
  
  
ADD INDEX (c); 
 
 

Note that we have indexed c, because the AUTO_INCREMENT column must be indexed, and we declare c as not null, because the indexed Column cannot be NULL.

When you add an AUTO_INCREMENT column, the column values are automatically entered with sequential numbers. The above content is an introduction to modifying the alter TABLE structure in MySQL. I hope you will find some gains.