mysql元数据相关查询

1.查询所有数据库中的decimal类型的列

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM information_schema.columns
WHERE DATA_TYPE = 'decimal';

2.查询所有数据库中的decimal(20,2)类型的列

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM information_schema.columns WHERE TABLE_SCHEMA = 'billionpay'     
AND DATA_TYPE = 'decimal'     AND COLUMN_TYPE LIKE 'decimal(20,2)%';

3.修改表字段语句

ALTER TABLE mytable
MODIFY total_tx_amount decimal(20, 2) DEFAULT '0.00' COMMENT 'test',
MODIFY total_st_amount decimal(20, 2) DEFAULT '0.00' COMMENT 'test2';