Sql-alter-change and Modify Differences

Alter for column application: 1. Change the format of column names: changes old_col_name new_col_name column_definition     Preserve old and new column names: A---->b column type ALTER TABLE T1 change a b INTEGER;　　   2. Change the column type without changing the column name to preserve the column name of old and new, even if it is the same. ALTER TABLE T1 change b b BIGINT not NULL;         3. Change the column type without changing the column name.　　   Old column names are not preserved. ALTER TABLE T1 MODIFY b BIGINT not NULL; Note: < 1> When using change or MODIFY, the definition of a column must include: the type of data and the attributes applied to the column, but not: index, for example: PRIMARY KEYOr UNIQUEFor example: the definition of a column: INT UNSIGNED DEFAULT 1 COMMENT ‘my column‘Change: Alter TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT ' my column ';<2> When you use change or modify to alter the data type, the value of the column is also converted to the corresponding column type whenever possible. Data may be lost, preferably set before using ALTER TABLE; strict SQL mode <3> Adds a column of the specified position, which can be First,after column_name, <4> use change  , modify shortens the length of the column, the column length is less than the corresponding index length, and the index is shortened automatically.           4. Change the default value for the column: Alter TABLE tb_name ALTER COLUMN_NAME. CREATE TABLE test2 (id INT, NAME VARCHAR (4));          Without specifying a default value, after the table is built: default NULL.           Change default value: Alter TABLE test2 alter name SET default '; Note: Changing the default value is only valid for the newly inserted data after the change. The previous data in the table, null or null, empty or empty.

Sql-alter-change and Modify Differences