如mysql何改表的字段参数_mysql 修改表的字段

修改一个表的字段

ALTER TABLE `member` CHANGE `memberid` `memberid` bigint unsigned;

修改含有外键的字段

-- 执行 begin 到 end 之间的代码可能需要修改sql_mode值

show variables like '%sql_mode'

-- 保存查询出来的sql_mode 值

set global sql_mode=''

set session sql_mode=''

-- begin

set group_concat_max_len = 2048;

set @table_name ="member";

set @change ="bigint unsigned";

set @table_id = "memberid";

select distinct table_name,

column_name,

constraint_name,

referenced_table_name,

referenced_column_name,

CONCAT(

GROUP_CONCAT('ALTER TABLE ',table_name,' DROP FOREIGN KEY ',constraint_name SEPARATOR ';'),

';\n',

GROUP_CONCAT('ALTER TABLE `',table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change SEPARATOR ';'),

';\n',

CONCAT('ALTER TABLE `',@table_name,'` CHANGE `',@table_id,'` `',@table_id,'` ',@change),

';\n',

GROUP_CONCAT('ALTER TABLE `',table_name,'` ADD CONSTRAINT `',constraint_name,'` FOREIGN KEY(',column_name,') REFERENCES ',referenced_table_name,'(',referenced_column_name,')' SEPARATOR ';')

) as query

from information_schema.key_column_usage

where referenced_table_name is not null

and referenced_column_name is not null

and referenced_table_name = @table_name

group by referenced_table_name

-- end

-- 恢复

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

执行begin到end之间的代码会得到 query 代码：

ALTER TABLE member_weixin DROP FOREIGN KEY member_weixin_ibfk_1;ALTER TABLE member_qq DROP FOREIGN KEY member_qq_ibfk_1;ALTER TABLE member_visitor DROP FOREIGN KEYmember_visitor_ibfk_1;ALTER TABLE `member_weixin` CHANGE `memberid` `memberid` bigint unsigned;ALTER TABLE `member_qq` CHANGE `memberid` `memberid` bigint unsigned;ALTER TABLE `member_visitor` CHANGE `memberid` `memberid` bigintunsigned;ALTER TABLE `member` CHANGE `memberid` `memberid` bigintunsigned;ALTER TABLE `member_weixin` ADD CONSTRAINT `member_weixin_ibfk_1` FOREIGN KEY(memberid) REFERENCES member(memberid);ALTER TABLE `member_qq` ADD CONSTRAINT `member_qq_ibfk_1` FOREIGN KEY(memberid) REFERENCES member(memberid);ALTER TABLE `member_visitor` ADD CONSTRAINT `member_visitor_ibfk_1` FOREIGN KEY(memberid) REFERENCES member(memberid)

执行query代码，即修改了字段。并且外键保持不变