MySQL批量修改表结构的方法

zhgc

select concat('alter table  smt.',table_name,'  modify   `WO` varchar(25);')  
from information_schema.COLUMNS where table_schema='smt'
and column_name ='WO' and character_maximum_length<16

zhgc

SELECT CONCAT('alter table  sfc.',table_name,'  modify   `',column_name,'` int;')  
FROM information_schema.COLUMNS WHERE table_schema='sfc'
AND column_name  LIKE '%ROUTE%' AND data_type='int'

zhgc

批量扩大GROUP的长度到25位

select table_schema,table_name,column_name,data_type,character_maximum_length,
character_octet_length,character_set_name,collation_name from information_schema.COLUMNS where column_name like '%GROUP%'
and data_type='varchar'  and character_maximum_length<25 order by character_maximum_length desc

zhgc

区分大小写的查询
select * from sfc.m_tx_list where BINARY  mo in ('a21090536','A21090511')

zhgc

select concat('alter table  smt.',table_name,'  modify   `TR_SN` varchar(50);')  
from information_schema.COLUMNS where table_schema='smt'
and column_name ='TR_SN' and character_maximum_length<32

syant

alter table `sfc`.`r_wip_tracking_t` modify    `CARTON_NO` varchar(200)   , modify    `MCARTON_NO` varchar(200)

alter table `sfc`.`r_wip_undo_t` modify    `CARTON_NO` varchar(200)   , modify    `MCARTON_NO` varchar(200)