Mysql Modify Table Structure Method Example detailed _mysql

This article describes the MySQL modified table structure method. Share to everyone for your reference. Specifically as follows:

MySQL Modify table structure using the ALTER TABLE statement, the following is a detailed description of the MySQL modify table structure statement, I hope that you learn the MySQL modify table structure can help.

ALTER [IGNORE] TABLE tbl_name alter_spec [, Alter_spec ...]
Alter_specification:
 ADD [COLUMN] create_definition [a] 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 Tab Le_options

ALTER TABLE allows you to modify the structure of an existing table. For example, you can add or delete columns, create or eliminate indexes, change the type of existing columns, or rename columns or the table itself. You can also change the annotation of the table and the type of the table.

If you use ALTER TABLE to modify a column description but describe tbl_name shows that your column has not been modified, it may be that MySQL ignores your modification because of one of the reasons described in the 7.7.1 implied column description change. For example, if you try to change a varchar to char,mysql, you will still use varchar if the table contains other variable-length columns.

ALTER table works by making a temporary pair of the original table. The modification is performed on the copy, and then the original table is deleted and renamed to a new one. This allows all changes to be automatically shifted to the new table without any failed modifications. When ALTER TABLE is executing, the original table can be read by another customer. Updates and write tables are deferred until the new table is ready.

In order to use ALTER TABLE, you need permissions on the table for SELECT, INSERT, delete, update, create, and drop.

Ignore is an extension of MySQL to ANSI SQL92, which controls how ALTER TABLE works if there is a duplicate on the unique key in the new table. If the ignore is not specified, the copy is waived and reinstated. If the ignore is specified, there is a duplicate row for the unique key, only the first row is used, and the remainder is deleted.

You can emit multiple add, alter, DROP, and change clauses in a single ALTER table statement. This is an extension of MySQL to ANSI SQL92, SQL92 only one clause is allowed in each ALTER TABLE statement.
Change col_name, Drop col_name, and drop index are MySQL extensions to the ANSI SQL92.
Modify is Oracle's extension of ALTER TABLE.

The optional Word column is a 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 that corresponds 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 column names, not just column types.

You can rename a column using the change old_col_name create_definition clause. In order to do so, specify the old and new column names and the current types of columns. For example, to rename an integer column, from A to B, you can do this:

Copy Code code as follows:

mysql> ALTER TABLE T1 change a b INTEGER;

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

Copy Code code as follows:

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 the column instead of renaming it:

Copy Code code as follows:

mysql> ALTER TABLE T1 MODIFY b BIGINT not NULL;

If you shorten a column with change or modify, an index exists in that column (for example, if you have an index of the first 10 characters of a varchar column), you cannot make the column shorter than the number of characters indexed.

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

After MySQL3.22 or later, you can use the one or add ... After col_name adds columns in a specific position within the row of a table. The default is to add to the last column.

ALTER column assigns a new default value to the column or deletes the old default value. If the old default is deleted and the column can be null, the new default value is null. If the column cannot be null,mysql give a default value. The default value assignment is described in the 7.7 CREATE table syntax.

Drop index deletes an index. This is an extension of MySQL to ANSI SQL92.

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

The drop PRIMARY key discards the primary index. If such an index does not exist, it discards the first unique index in the table. (if the primary key,mysql tag is not explicitly specified, the first unique key is primary key.) ）

With the C API function Mysql_info (), you can find out how many records are copied and (when using ignore) how many records are deleted because of unique key values.

FOREIGN KEY, check, and references clauses do nothing, their syntax simply provides compatibility, making it easier to migrate code from other SQL Servers and run applications that create tables with references. See 5.4 MySQL lacks functionality.

Here is an example that shows some ALTER table usages. Let's start with a table T1 created as follows:

Copy Code code as follows:

mysql> CREATE TABLE T1 (a integer,b CHAR (10));

Rename table, from T1 to T2:

Copy Code code as follows:

mysql> ALTER TABLE t1 RENAME T2;

In order to alter column A, change from integer to tinyint not NULL (the first name) and change column B from char (10) to char (20), rename it at the same time, change from B to C:

Copy Code code as follows:

mysql> ALTER TABLE T2 MODIFY a TINYINT not NULL and change B C CHAR (20);

Add a new timestamp column, named D:

Copy Code code as follows:

mysql> ALTER TABLE T2 ADD D TIMESTAMP;

Add an index to column D and make column a the primary key:

Copy Code code as follows:

mysql> ALTER TABLE T2 add INDEX (d), add PRIMARY KEY (a);

Delete Column C:

Copy Code code as follows:

mysql> ALTER TABLE T2 DROP COLUMN C;

Add a new auto_increment integer column named C:

Copy Code code as follows:

mysql> ALTER TABLE T2 add c INT UNSIGNED not NULL auto_increment, add INDEX (c);

Notice that we indexed C because the Auto_increment column had to be indexed and in addition we declared C to not NULL because the indexed column could not be null.

When you add a auto_increment column, automatically fill in the row value with the sequential number.

I hope this article is helpful to the design of MySQL database.