[转帖]mysql 修改字段,字符集,alter table 优化_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1770 | 回复: 0   主题: [转帖]mysql 修改字段,字符集,alter table 优化        下一篇 
ad222888
注册用户
等级:新兵
经验:66
发帖:134
精华:0
注册:2016-9-25
状态:离线
发送短消息息给ad222888 加好友    发送短消息息给ad222888 发消息
发表于: IP:您无权察看 2018-8-28 16:04:56 | [全部帖] [楼主帖] 楼主

一次添加一个字段:

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才能生效.




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论