在系统开发中,进行了分库分表操作,产生了多个数据库,每个数据库有多表,每张表有多个字段。系统的每个阶段开发,对同一个字段的设计,类型,长度,注释等可能会有区别,会导致,相同的字段的属性为什么不一样,不规范,需要进行同一修改,由于数据库表非常多,那么怎样快速的查询相关信息,以及快速进行修改,生成相应的SQL语句, 以及查找没有主键的数据库表
此篇文章包含一下内容:
1. 快速找出某些字段在哪些表的相关SQL信息
2. 快速生成某些字段修改ddl的SQL语句
3. 查找没有主键的数据库表
例: 在数据库链接(127.0.0.1:3306)下有一下数据库和数据库表,其具体信息,关系如下表:
basedb(基础信息库) | t_customer (客户信息表) | `cust_id` varchar(25) NOT NULL COMMENT '客户号' |
`cust_name` varchar(30) DEFAULT NULL COMMENT '客户姓名' | ||
add_time varchar(20) DEFAULT NULL COMMENT '添加时间' | ||
t_fund_account(资金账号表) | `cust_id` varchar(50) DEFAULT NULL COMMENT '客户号' | |
`fund_id` varchar(25) NOT NULL COMMENT '资金账号ID' | ||
`update_time varchar(20) DEFAULT NULL COMMENT '更新时间' | ||
`market_id` varchar(50) DEFAULT NULL COMMENT '市场' | ||
t_account (账户表) | `account_id` varchar(25) NOT NULL COMMENT '账号ID' | |
`cust_id` varchar(255) DEFAULT NULL COMMENT '客户号' | ||
`market_id` varchar(10) DEFAULT NULL COMMENT '市场' | ||
add_time varchar(20) DEFAULT NULL COMMENT '添加时间' | ||
`update_time varchar(30) DEFAULT NULL COMMENT '更新时间' | ||
`fund_id` varchar(65) DEFAUL NULL COMMENT '资金账号号ID' | ||
importdb(导入库) | t_import(导入表) | `cust_id` varchar(20) DEFAULT NULL COMMENT '客户ID' |
add_time varchar(20) DEFAULT NULL COMMENT '添加时间' | ||
`update_time varchar(30) DEFAULT NULL COMMENT '更新时间' | ||
tradingdb (交易库) | t_order (订单表) | `cust_id` varchar(15) DEFAULT NULL COMMENT '客户号' |
`fund_id` varchar(25) DEFAUL NULL COMMENT '资金账号ID' | ||
`market_id` varchar(8) DEFAULT NULL COMMENT '市场' | ||
t_report(回报表) | `cust_id` varchar(20) DEFAULT NULL COMMENT '客户ID' | |
`market_id` varchar(5) DEFAULT NULL COMMENT '市场' | ||
`account_id` varchar(25) NOT NULL COMMENT '正账号ID' |
从表1中可以得知,相同的数据库链接中,有 3个数据库,共有6张数据库表,各个数据表中度包含相同的字段名,但是字段的长度不一致,或者注释不正确
1. 快速找出相关的信息,如下SQL
SELECT
COLUMN_NAME,
GROUP_CONCAT( COLUMN_COMMENT ),
GROUP_CONCAT( TABLE_NAME ),
GROUP_CONCAT( DISTINCT CHARACTER_MAXIMUM_LENGTH ),
GROUP_CONCAT( DISTINCT COLUMN_TYPE )
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA IN ( 'basedb', 'importdb', 'tradingdb' )
AND COLUMN_NAME IN ( 'cust_id', 'fund_id', 'account_id', 'market_id', 'add_time', 'update_time' )
AND CHARACTER_MAXIMUM_LENGTH > 2
GROUP BY
COLUMN_NAME
2. 快速生成修改ddl的SQL语句:
SELECT
CONCAT(
'ALTER TABLE ',
'`',
TABLE_SCHEMA,
'`',
'.',
'`',
TABLE_NAME,
'`',
' MODIFY COLUMN ',
'`',
COLUMN_NAME,
'`',
' ',
DATA_TYPE,
'(',
CHARACTER_MAXIMUM_LENGTH,
')',
IF
( IS_NULLABLE = "YES", ' DEFAULT NULL COMMENT ', ' NOT NULL COMMENT ' ),
'"',
COLUMN_COMMENT,
'"',
';'
) as alter_table_ddl
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA IN ( 'basedb', 'importdb', 'tradingdb' )
AND COLUMN_NAME IN ( 'cust_id', 'fund_id', 'account_id', 'market_id', 'add_time', 'update_time' )
AND CHARACTER_MAXIMUM_LENGTH > 2
3. 快速查找所有的数据库中的数据库表中没有主键的表
SELECT
table_schema,
table_name
FROM
information_schema.TABLES
WHERE
( table_schema, table_name ) NOT IN ( SELECT DISTINCT table_schema, table_name FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI' ) -- 排除系统数据库表
AND table_schema NOT IN ( 'sys', 'mysql', 'information_schema', 'performance_schema' );