79447

Column Properties

•    Set a default value for a column: alter table tbl alter column col SET DEFAULT

literał OrALTER TABLE tbl ALTER COLUMN col DROP DEFAULT The default values for columns are stored in the .frm file for the table, not the innoDB data dictionary.

•    Changing the auto-increment value for a column: ALTER TABLE table AUTO_INC REMENT=next_value;

Especially in a distributed system using replication or sharding, you sometimes reset the autoincrement

counter for a table to a specific value. The next row inserted into the table uses

the specified value for its auto-increment column. You might also use this technique in a data

warehousing environment where you periodically empty all the tables

and reload them, and you can

restart the auto-increment sequence from 1.

•    Renaming a column: alter table tbl change old_col_name new_col_name datatype

When you keep the same data type and [NOT] NULL attribute, only changing the column name, this operation can always be performed online.

As part of this enhancement, you can now rename a column that is part of a foreign key constraint,

which was not allowed before. The foreign key definition is automatically updated to use the new

column name. Renaming a column participating in a foreign key only works with the in-place modę

of alter table. If you use the algorithm=copy clause, or some other condition causes the

command to use algorithm=copy behind the scenes, the alter table statement will fail.