修改mysql字段长度

何处是归途

已于 2022-08-26 14:30:37 修改

阅读量8.8k

点赞数 1

分类专栏： sql 文章标签： mysql 数据库

于 2022-08-26 14:29:28 首次发布

本文链接：https://blog.csdn.net/qq_38013013/article/details/126542505

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、需求：

由于之前设计的数据库主键字段太小无法使用uudi等不重复主键导致项目性能问题因此需要

将数据库所有的表字段长度小于35大于15的字段全部扩容到60，字段类型是varchar

2、获取修改字段的sql

注意：单纯的使用alter t_table MODIFY column column_name varchar(60);会导致字段原先的默认值和备注等信息丢失，这是已经影响到代码的逻辑了，是不能接受的

select concat('alter table `',table_name,
    '` MODIFY column `',column_name,
    '` varchar(60) CHARACTER SET ',Character_set_name,
    ' COLLATE ',Collation_name,
     if(is_nullable='NO',' NOT NULL ',' NULL ') ,
     if(column_default is null ,' ',concat(' DEFAULT \'', column_default,'\'')) ,
    ' COMMENT \'',COLUMN_comment,'\';') as sqlz 
from information_schema.COLUMNS 
where 1=1
	and table_schema = 'schema数据库名称' 
	and data_type = 'varchar'
	and CHARACTER_maximum_LENGTH<35
	and CHARACTER_maximum_LENGTH>15
	and table_name in   
    (select table_name 
            from information_schema.tables 
            where table_schema = 'schema数据库名称' 
            and  table_type = 'BASE TABLE');

3、执行

alter table `t_table` MODIFY column `Townid` varchar(60) 
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL   COMMENT '镇街';

4、其他：

遇到的问题：由于老版本的数据库在设计的时候时间字段默认值使用了：

'0000-00-00 00:00:00'导致在修改其他字段报错，提示该字段默认值有误

临时解决方案是：

-- 获取当前sql_mode
SELECT @@SESSION.sql_mode;
-- 在执行修改命令的开头先执行修改sql-mode 注意需要去掉 NO_ZERO_IN_DATE,NO_ZERO_DATE
 set @@SESSION.sql_mode=
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';