一次添加一个字段:
ALTER TABLE 表名称 ADD COLUMN 字段名 VARCHAR(20) NULL COMMENT '新添加的字段' AFTER `前一个字段名`;
Comment 表示注释;
After `前一个字段名` 可以省略,默认是将新字段添加在表的最后一列,如果想将字段放在某个字段的后面, 可以加after `前一个字段名` 指定;
一次添加多个字段:
ALTER TABLE 表名称 add(
字段1 TINYINT NULL COMMENT 'xxxx ',
字段2 TINYINT NULL COMMENT 'yyyy'
一次删除一个字段:
ALTER TABLE 表名称 DROP COLUMN 字段名;
一次删除多个字段:
ALTER TABLE 表名称 DROP COLUMN 字段1, DROP COLUMN 字段2;
修改字段名称:
ALTER TABLE 表名称 CHANGE 旧字段名 新字段名 varchar(10) NULL COMMENT '注释文字';
修改字段的类型:
ALTER TABLE 表名称 MODIFY COLUMN 字段名 INT NULL COMMENT '注释文字';
修改单个字段的字符集:
ALTER TABLE 表名称 MODIFY COLUMN 字段名 VARCHAR(80) CHARACTER SET utf8mb4 NOT NULL COMMENT '注释文字';
修改所有字段的字符集(仅对字符类型的字段有效,不会修改int等这种非字符的字段):
ALTER TABLE 表名称 CONVERT TO CHARACTER SET utf8mb4;
修改默认字符集:
ALTER TABLE 表名称 EFAULT CHARACTER SET gbk;
改的是下方ENGINE=InnoDB 后面的 DEFAULT CHARSET=gbk;
CREATE TABLE `zzz` (
`names` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`kecheng` varchar(80) CHARACTER SET utf8mb4 NOT NULL COMMENT '课程',
`score` int(11) DEFAULT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Alter table 优化:
Alter table 要慎重, mysql内部是按照新的表结构建一个新表, 然后把旧表中的数据插入到新表, 然后删除旧表,然后将新表重命名为旧表名.如果表中数据较多,alter过程将非常长.
如下3种情况可以进行优化(避免表重建):
(1) 更改字段的默认值
(2) 增加/删除字段的AUTO_INCREMENT属性
(3) 增加/删除/修改 ENUM的常量值。对于删除操作,如果有字段引用了这个常量值,则在删除后查询的结构为空字符串
针对上述3种情况, 有两种方式可以避免表重建:
假设现在的表名称叫 users , 要将字段gender 的默认值从0 改成1,两种方式分别如下:
方式一:
ALTER TABLE s_user ALTER COLUMN gender SET DEFAULT 1;
而不是使用
ALTER TABLE s_user MODIFY COLUMN gender TINYINT NULL DEFAULT 1;
第一条sql不会引起表重建, 而第二条sql会引起表重建.
方式二:
因为这3部分数据存储在表的.frm(myisam引擎结构文件有三种:.frm:表结构文件 .MYD:表数据文件 .MYI:表索引, innodb有两种: . frm:表结构文件, .ibd:数据和索引文件是同一个. MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而InnoDB是聚集索引,就是索引文件节点中就包含了完整的数据记录)文件中,可以用新的.frm文件替换旧表的.frm文件来达到修改的目的,已亲测可用.
Create table users_new like users; -- 建新表
Alter table users_new alter column gender SET DEFAULT 1; -- 改新表的gender默认值
然后进入到mysql的data目录, 将users.frm 重命名为 users.frm.bak, 将users_new.frm复制一份重命名为users.frm, 重启mysql才能生效.