我要评论前言：在mysql中 concat ()函数用于将多个字符串连接成一个字符串，利用此函数我们可以将原来一步无法得到的sql拼接出来，在工作中也许会方便很多，下面主要介绍下几个常用的场景。
注：适用于5.7版本 低版本可能稍许不同。
1.拼接查询所有用户
select distinct
concat(
'user: \'',
user,
'\'@\'',
host,
'\';'
) as query
from
mysql.user;
# 当拼接字符串中出现'时 需使用\转义符
2.拼接drop table
select
concat(
'drop table ',
table_name,
';'
)
from
information_schema. tables
where
table_schema = 'test';
3.拼接kill连接
select
concat('kill ', id, ';')
from
information_schema. processlist
where
state like 'creating sort index';
4.拼接创建数据库语句
select
concat(
'create database ',
'`',
schema_name,
'`',
' default character set ',
default_character_set_name,
';'
) as createdatabasequery
from
information_schema.schemata
where
schema_name not in (
'information_schema',
'performance_schema',
'mysql',
'sys'
);
5.拼接创建用户的语句
select
concat(
'create user \'',
user,
'\'@\'',
host,
'\''
' identified by password \'',
authentication_string,
'\';'
) as createuserquery
from
mysql.`user`
where
`user` not in (
'root',
'mysql.session',
'mysql.sys'
);
#有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户
6.导出权限脚本 这个shell脚本也用到了拼接
#!/bin/bash
#function export user privileges
pwd=yourpass
expgrants()
{
mysql -b -u'root' -p${pwd} -n $@ -e "select concat( 'show grants for ''', user, '''@''', host, ''';' ) as query from mysql.user" | \
mysql -u'root' -p${pwd} $@ | \
sed 's/\(grant .*\)/\1;/;s/^\(grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql
7.查找表碎片
select t.table_schema,
t.table_name,
t.table_rows,
concat(round(t.data_length / 1024 / 1024, 2), 'm') as size,
t.index_length,
concat(round(t.data_free / 1024 / 1024, 2), 'm') as datafree
from information_schema.tables t
where t.table_schema = 'test' order by data_length desc;
8.查找无主键表 这个没用到拼接 也分享出来吧
#查找某一个库无主键表
select
table_schema,
table_name
from
information_schema.tables
where
table_schema = 'test'
and table_name not in (
select
table_name
from
information_schema.table_constraints t
join information_schema.key_column_usage k using (
constraint_name,
table_schema,
table_name
)
where
t.constraint_type = 'primary key'
and t.table_schema = 'test'
);
#查找除系统库外 无主键表
select
t1.table_schema,
t1.table_name
from
information_schema. tables t1
left outer join information_schema.table_constraints t2 on t1.table_schema = t2.table_schema
and t1.table_name = t2.table_name
and t2.constraint_name in ('primary')
where
t2.table_name is null
and t1.table_schema not in (
'information_schema',
'performance_schema',
'mysql',
'sys'
) ;
以上就是mysql 常用的拼接语句汇总的详细内容，更多关于mysql 拼接语句的资料请关注移动技术网其它相关文章！
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论