修改字段定义的时候,要把整个字段的全部的定义都写上,举个栗子:
原字段定义:
state varchar(50) NOT NULL DEFAULT '' COMMENT '产品资料状态'
需求:修改字段长度为100
正确的写法:
alter table t1 modify state varchar(100) NOT NULL DEFAULT '' COMMENT '产品资料状态'
错误的写法:
alter table t1 modify state varchar(100) //此操作会将NOT NULL DEFAULT '' COMMENT '产品资料状态' 信息清掉,state的定义只有 varchar(100)。
这种问题会发生在mysql、tidb中,需要特别注意,Oracle中则没有这种问题。
下面是测试过程:
mysql:修改列属性时需要写上comment完成语句
17:25:52[5.7.25-log]root->192.168.30.10[mtest]> create table t1 (id int,name varchar(100)); alter table t1 modify name varchar(100) COMMENT 'test_comment'; show create table t1; Query OK, 0 rows affected (0.04 sec) 17:27:30[5.7.25-log]root->192.168.30.10[mtest]> alter table t1 modify name varchar(100) COMMENT 'test_comment'; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 17:27:30[5.7.25-log]root->192.168.30.10[mtest]> show create table t1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL COMMENT 'test_comment' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) --修改列属性后comment信息丢失 17:27:30[5.7.25-log]root->192.168.30.10[mtest]> alter table t1 modify name varchar(200); show create table t1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 17:33:26[5.7.25-log]root->192.168.30.10[mtest]> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(200) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) --正确的操作 17:33:26[5.7.25-log]root->192.168.30.10[mtest]> alter table t1 modify name varchar(200) COMMENT 'test_comment'; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 17:35:08[5.7.25-log]root->192.168.30.10[mtest]> show create table t1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(200) DEFAULT NULL COMMENT 'test_comment' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
tidb:修改列属性时需要写上comment完成语句
17:23:43[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> create table t1 (id int,name varchar(100)); alter table t1 modify name varchar(100) COMMENT 'test_comment'; Query OK, 0 rows affected (0.16 sec) 17:27:01[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> alter table t1 modify name varchar(100) COMMENT 'test_comment'; Query OK, 0 rows affected (0.17 sec) 17:27:02[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL COMMENT 'test_comment' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) --修改列属性后comment信息丢失 17:27:10[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> alter table t1 modify name varchar(200); show create table t1; Query OK, 0 rows affected (0.12 sec) 17:33:20[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> show create table t1; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(200) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) --正确的操作 17:33:20[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> alter table t1 modify name varchar(200) COMMENT 'test_comment'; show create table t1; Query OK, 0 rows affected (0.13 sec) 17:35:12[5.7.25-TiDB-v4.0.5]root->192.168.30.10[mtest]> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(200) DEFAULT NULL COMMENT 'test_comment' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
oracle:修改列属性不影响comment
17:25:58 SYS@orcl11g>create table t1 (id int,name varchar2(100)); Table created. 17:27:41 SYS@orcl11g>comment on column t1.name is 'test_comment'; Comment created. --oracle中看到的表结构 CREATE TABLE "SYS"."T1" ( "ID" NUMBER(*,0), "NAME" VARCHAR2(100) ) ; COMMENT ON COLUMN "SYS"."T1"."NAME" IS 'test_comment'; --修改列属性后再看下表结构 17:27:45 SYS@orcl11g>alter table t1 modify name varchar2(200); Table altered. CREATE TABLE "SYS"."T1" ( "ID" NUMBER(*,0), "NAME" VARCHAR2(200) ) ; COMMENT ON COLUMN "SYS"."T1"."NAME" IS 'test_comment';