mysql modified table structure method example details

This article gives an example of mysql's approach to modifying table structure. Share to everybody for everybody reference. The details are as follows:

mysql USES ALTER TABLE statement to modify table structure. The following is a detailed introduction of mysql statement to modify table structure. I hope it will be helpful for you to learn mysql to modify table structure.

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 remove columns, create or eliminate indexes, change the type of existing columns, or rename columns or tables themselves. You can also change table comments and table types.

If you modified a column specification using ALTER TABLE but DESCRIBE tbl_name shows that your column has not been modified, this may be because MySQL ignored your modification because of the reason 1 described in the implied column specification change in 7.7.1. For example, if you try to change 1 VARCHAR to CHAR, MySQL will still use VARCHAR if the table package contains other variable-length columns.

ALTER TABLE works by making a temporary copy of the original table. The modification is performed on the replica, then the original table is deleted and a new one is renamed. This makes all changes automatically redirected to the new table without any failed changes. While the ALTER TABLE is executing, the original table can be read by another customer. Updating and writing tables are deferred until the new table is ready.

In order to use ALTER TABLE, you need permissions on select, insert, delete, update, create, and drop on the table.

IGNORE is an extension of MySQL to ANSI SQL92, which controls how ALTER TABLE works if there is duplication on the only key in the new table. If IGNORE is not specified, the copy is discarded and reverted. If IGNORE is specified, only the first row will be used if there are duplicate rows for the only 1 key. The rest is deleted.

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement. This is an extension of MySQL to ANSI SQL92, which allows only one clause per ALTER TABLE statement.
CHANGE col_name, DROP col_name and DROP INDEX are extensions of ANSI SQL92 by MySQL.
MODIFY is an extension of Oracle to ALTER TABLE.

The optional word COLUMN is 1 pure noise and can be omitted.

If you use ALTER TABLE tbl_name RENAME AS new_name without any other options, MySQL simply renames the file corresponding to table tbl_name. There is no need to create temporary tables.

The create_definition clause USES the same ADD and CHANGE syntax as CREATE TABLE. Note that the syntax includes column names, not just column types.

You can rename a column using the CHANGE old_col_name create_definition clause. To do this, specify the old and new column names and the type that the column currently has. For example, if you 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 column types instead of names, even if they are one, the CHANGE syntax still requires two column names. Such as:

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

However, in MySQL3.22.16a, you can also use MODIFY to change the type of a column instead of renaming it:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

If you use CHANGE or MODIFY to shorten 1 column and 1 index exists in the portion of that column (for example, if you have an INDEX of the first 10 characters of an VARCHAR column), you cannot make the column shorter than the number of characters being indexed.

When you change a column type using CHANGE or MODIFY, MySQL tries to transform the data to the new type as well as possible.

After MySQL3.22 or later, you can use FIRST or ADD... AFTER col_name adds columns at a specific location within a row of a table. The default is to increase to the last column.

ALTER COLUMN specifies new defaults for the column or removes the old ones. If the old default is removed and the column can be NULL, the new default is NULL. MySQL assigns a default value if the column cannot be NULL. The default assignment is described in the 7.7 CREATE TABLE syntax.

DROP INDEX removes 1 index. This is an extension of MySQL to ANSI SQL92.

If columns are dropped from a table, columns are also dropped from any index to which they are a part. If all the columns that make up an index are discarded, the index is also discarded.

DROP PRIMARY KEY discard 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.)

With the C API function mysql_info(), you can find out how many records were copied and (when using IGNORE) how many records were deleted due to duplications of only one key value.

The FOREIGN KEY, CHECK, and REFERENCES clauses do virtually nothing, and their syntax merely provides compatibility, making it easier to migrate code from other SQL servers and run applications that create tables by reference. See 5.4 Features missing from MySQL.

Here is an example showing some ALTER TABLE usages. We start with a table t1 created as follows:

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

Rename tables from t1 to t2:

mysql> ALTER TABLE t1 RENAME t2;

To change column a, change from INTEGER to TINYINT NOT NULL(name 1 sample), and change column b, change from CHAR(10) to CHAR(20), and rename it 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 to column d and make column a the primary key:

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

c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Add a new AUTO_INCREMENT integer column and name it 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 in addition we declare c NOT NULL because the indexed column cannot be NULL.

When you add an AUTO_INCREMENT column, the column values are automatically filled in with sequential Numbers.

I hope this article has been helpful for your MySQL database programming.