查看某数据库中表信息
SELECT * from information_schema.tables
WHERE table_schema not in ('information_schema', 'mysql', 'performance_schema')
and table_schema='zaip_xman_avatar'
ORDER BY table_schema, table_name;
根据表名查询所有表字段
select group_concat(COLUMN_NAME) from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'
id,ds_name,ds_desc,ds_type,ds_address,ds_cluster_name,access_id,access_key,second_level_code,creator,modifier,gmt_created,gmt_modified,is_deleted,tenant_code
防止字段名是关键字导致异常
select concat('`',group_concat(column_name separator '`,`'),'`') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'
id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,expire_time_start,expire_time_end,expire_day,delivery_time,status,is_deleted,creator,gmt_created,modifier,gmt_modified
第二种方式，比上面一种方便
select group_concat(concat('`',column_name,'`') separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'
id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,expire_time_start,expire_time_end,expire_day,delivery_time,status,is_deleted,creator,gmt_created,modifier,gmt_modified
直接将column_name替换为：
concat('`',column_name,'`')
根据字段类型写对应的表达式
select group_concat(case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',column_name,')*1000') else column_name end separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'
上面是datetime转换成时间戳对应的sql
id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,UNIX_TIMESTAMP(expire_time_start)1000,UNIX_TIMESTAMP(expire_time_end)1000,expire_day,UNIX_TIMESTAMP(delivery_time)1000,status,is_deleted,creator,UNIX_TIMESTAMP(gmt_created)1000,modifier,UNIX_TIMESTAMP(gmt_modified)*1000
直接将column_name替换为：
case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',column_name,')*1000') else column_name end
防止字段名是关键字
select group_concat(case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',concat('`',column_name,'`'),')*1000') else concat('`',column_name,'`') end separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'
id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,UNIX_TIMESTAMP(expire_time_start)1000,UNIX_TIMESTAMP(expire_time_end)1000,expire_day,UNIX_TIMESTAMP(delivery_time)1000,status,is_deleted,creator,UNIX_TIMESTAMP(gmt_created)1000,modifier,UNIX_TIMESTAMP(gmt_modified)*1000
根据字段名称写对应表达式
select group_concat(case when column_name in ('gmt_created','gmt_modified') then concat('UNIX_TIMESTAMP(',column_name,')*1000') else column_name end separator ',') from information_schema.COLUMNS where table_name = 'coupon' and table_schema='zaip_xman_activity'
id,prize_code,parent_id,coupon_code,coupon_cdkey,expire_type,expire_time,expire_time_start,expire_time_end,expire_day,delivery_time,status,is_deleted,creator,UNIX_TIMESTAMP(gmt_created)1000,modifier,UNIX_TIMESTAMP(gmt_modified)1000
生成查询数据库sql脚本
delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200))
begin
declare sqltext longtext default '';
declare num,dataNum int default 0;
set num=1;
set @rowNo=0;
drop table if exists temp;
create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and table_schema=databaseName ORDER BY table_schema, table_name;
while num<=@rowNo do
select concat(sqltext,'\n','select ',group_concat(COLUMN_NAME),' from ',table_name,';') into sqltext from information_schema.COLUMNS where table_name in (select table_name from temp where id=num);
set num=num+1;
end while;
drop table if exists temp;
select sqltext;
end $$
delimiter ;
-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;
-- 执行存储过程
call pro('zaip_xman_avatar');
GROUP_CONCAT超长截断问题
ROUP_CONCAT拼接的最大长度默认为1024字节，超过这个长度则会被截断，可以通过SET [GLOBAL | SESSION] group_concat_max_len = val;进行修改。
比如修改GROUP_CONCAT长度为1个字节。
SET SESSION group_concat_max_len = 1;
生成Insert into select数据库sql脚本
初始版，只有查询的库名，必须在要新增数据的库中运行
delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin
declare sqltext longtext default '';
declare num,dataNum int default 0;
set num=1;
set @rowNo=0;
drop table if exists temp;
create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and table_schema=databaseName ORDER BY table_schema, table_name;
while num<=@rowNo do
select concat(sqltext,'\n','insert ignore into ',table_name,' (',group_concat(column_name),') \n','select',lpad(' ',length(table_name)+15,' '),group_concat(COLUMN_NAME),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
set num=num+1;
end while;
drop table if exists temp;
select sqltext;
end $$
delimiter ;
-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;
-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');
新增insert库名
delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin
declare sqltext longtext default '';
declare num,dataNum int default 0;
set num=1;
set @rowNo=0;
drop table if exists temp;
create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and table_schema=databaseName ORDER BY table_schema, table_name;
while num<=@rowNo do
select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(column_name),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(COLUMN_NAME),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
set num=num+1;
end while;
drop table if exists temp;
select sqltext;
end $$
delimiter ;
-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;
-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');
call pro('zaip_xman_multi_tenant_new','zaip_xman_activity');
每个字段用`标志，防止字段名是关键字
delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin
declare sqltext longtext default '';
declare num,dataNum int default 0;
set num=1;
set @rowNo=0;
drop table if exists temp;
create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and table_schema=databaseName ORDER BY table_schema, table_name;
while num<=@rowNo do
select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(concat('`',column_name,'`')),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(concat('`',column_name,'`')),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
set num=num+1;
end while;
drop table if exists temp;
select sqltext;
end $$
delimiter ;
-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;
-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');
call pro('zaip_xman_multi_tenant_new','zaip_xman_activity');
将datetime转换成时间戳，自动生成语句
delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin
declare sqltext longtext default '';
declare num,dataNum int default 0;
set num=1;
set @rowNo=0;
drop table if exists temp;
create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and table_schema=databaseName ORDER BY table_schema, table_name;
while num<=@rowNo do
select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(concat('`',column_name,'`')),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(case data_type when 'datetime' then concat('UNIX_TIMESTAMP(',concat('`',column_name,'`'),')*1000') else concat('`',column_name,'`') end),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
set num=num+1;
end while;
drop table if exists temp;
select sqltext;
end $$
delimiter ;
-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;
-- 执行存储过程
call pro('zaip_xman_avatar_new','zaip_xman_avatar');
call pro('zaip_xman_multi_tenant_new','zaip_xman_activity');
call pro('zaip_xman_xflow_new','zaip_xman_xflow');
生成的sql语句：
insert ignore into zaip_xman_xflow_new.zatlas_request_log (`id`,`time`,`email`,`appkey`,`remote_addr`,`method`,`uri`,`response_time`,`token`,`status`,`gmt_created`,`gmt_modified`,`creator`,`modifier`,`is_deleted`)
select `id`,UNIX_TIMESTAMP(`time`)*1000,`email`,`appkey`,`remote_addr`,`method`,`uri`,`response_time`,`token`,`status`,UNIX_TIMESTAMP(`gmt_created`)*1000,UNIX_TIMESTAMP(`gmt_modified`)*1000,`creator`,`modifier`,`is_deleted` from zaip_xman_xflow.zatlas_request_log;
根据字段名判断类型，自动生成语句
delimiter $$
drop procedure if exists pro $$
create procedure pro(databaseName varchar(200),originDataBaseName varchar(200))
begin
declare sqltext longtext default '';
declare num,dataNum int default 0;
set num=1;
set @rowNo=0;
drop table if exists temp;
create table temp as SELECT (@rowNO := @rowNo+1) AS id,table_name from information_schema.tables WHERE table_name<>'temp' and table_schema=databaseName ORDER BY table_schema, table_name;
while num<=@rowNo do
select concat(sqltext,'\n','insert ignore into ',databaseName,'.',table_name,' (',group_concat(concat('`',column_name,'`')),') \n','select',lpad(' ',length(databaseName)+1,' '),lpad(' ',length(table_name)+15,' '),group_concat(case when column_name in ('gmt_created','gmt_modified','attribution_begin_date','attribution_end_date','last_warning_time','effective_time','begin_date','end_date','analyze_begin_date','analyze_end_date','funnel_begin_date','funnel_end_date','bizdate_date') then concat('UNIX_TIMESTAMP(',concat('`',column_name,'`'),')*1000') else concat('`',column_name,'`') end),' from ',originDataBaseName,'.',table_name,';') into sqltext from information_schema.COLUMNS where table_schema=databaseName and table_name in (select table_name from temp where id=num);
set num=num+1;
end while;
drop table if exists temp;
select sqltext;
end $$
delimiter ;
-- 防止group_concat截断
SET SESSION group_concat_max_len = 999999999;
call pro('zaip_xman_xflow_new','zaip_xman_xflow');
将字段名'gmt_created','gmt_modified','attribution_begin_date','attribution_end_date','last_warning_time','effective_time','begin_date','end_date','analyze_begin_date','analyze_end_date','funnel_begin_date','funnel_end_date','bizdate_date'字段当成时间戳类型查出
校验mysql
校验mysql库里面表数量
SELECT table_schema,count(1) as num from information_schema.tables
WHERE table_schema in ('zaip_xman_activity_new','zaip_xman_avatar_new','zaip_xman_cms_new','zaip_xman_common_mgt_new','zaip_xman_lang_new','zaip_xman_markting_new','zaip_xman_multi_tenant_new','zaip_xman_push_new','zaip_xman_xflow_new')
GROUP BY table_schema;
校验对比迁移数据量是否一致
delimiter $$
drop procedure if exists testMigrate $$
create procedure testMigrate(databaseName varchar(200),originDataBaseName varchar(200))
begin
SELECT
a.table_name,
a.table_rows as newTableRows,
b.table_rows as oldTableRows
FROM
information_schema.tables a
join information_schema.tables b
on a.table_name=b.table_name
WHERE
a.table_schema = databaseName
and b.table_schema = originDataBaseName
and a.table_type = 'BASE TABLE'
and b.table_type = 'BASE TABLE'
and a.table_rows<>b.table_rows;
end $$
delimiter ;
call testMigrate('zaip_xman_xflow_new','zaip_xman_xflow');
为什么mysql没有nolock
- SQLSERVER 默认情况下,一个事务修改了某个值，在这个事务提交前，是阻塞其他连接来读取这个修改中的值的，如果加nolock，那么读取到的可能是修改后未提交的值(也就是脏读，因为可能这个值最终会回滚)
- MySQL 一个事务修改了某个值，在这个事务提交前，不阻塞其他连接来读取这个修改中的值，并且读取到的是修改前的值。
根据上面的对比，发现mysql使用nolock没有意义，因为mysql本身就不阻塞查询。
参考
insert into ... select 由于SELECT表引起的死锁情况分析
mysql - 索引/fulltext index/force index/use force /ignore index/索引失效