方法:通过sql语句拼凑出可执行的sql语句
把mysql中列名大写字母列转化为小写的sql
SELECT
concat(
'alter table ', TABLE_NAME,
' change column ', COLUMN_NAME, ' ',
LOWER( COLUMN_NAME ), ' ',
COLUMN_TYPE,
' comment \'',COLUMN_COMMENT, '\';' ) AS 修改脚本
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = '数据库名称';
生成批量修改数据库中表的相关列的字段类型和备注的sql语句
SELECT
concat(
'alter table ', TABLE_NAME,
' change column ', COLUMN_NAME, ' ',
LOWER( COLUMN_NAME ), ' ',
'varchar(20)',
' comment \'',
'这里是列备注', '\';' ) AS 修改脚本
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'basemdm'
AND column_name = '列名';
查询数据库里面的表名,字段名,类型,备注
select table_name 表名,
column_name 字段名,
column_type 字段类型和长度,
column_comment 字段注释
from information_schema.columns
where 1 = 1
and table_schema = '数据库名'
order by table_name, ordinal_position;
查询数据库里面的表名,表注释
select table_name 表名,
TABLE_COMMENT 表注释
from information_schema.TABLES
where 1 = 1
and table_schema = 'basemdm'