前言：在MySQL中 CONCAT ()函数用于将多个字符串连接成一个字符串，利用此函数我们可以将原来一步无法得到的sql拼接出来，在工作中也许会方便很多，下面主要介绍下几个常用的场景。
注：适用于5.7版本 低版本可能稍许不同。
1.拼接查询所有用户
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT DISTINCT CONCAT( 'User: \'', USER, '\'@\'', HOST, '\';' ) AS QUERYFROM mysql.USER;# 当拼接字符串中出现'时 需使用\转义符 |
2.拼接DROP table
|
1
2
3
4
5
6
7
8
9
10
|
SELECT CONCAT( 'DROP table ', TABLE_NAME, ';' )FROM information_schema. TABLESWHERE TABLE_SCHEMA = 'test'; |
3.拼接kill连接
|
1
2
3
4
5
6
|
SELECT concat('KILL ', id, ';')FROM information_schema. PROCESSLISTWHERE STATE LIKE 'Creating sort index'; |
4.拼接创建数据库语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SELECT CONCAT( 'create database ', '`', SCHEMA_NAME, '`', ' DEFAULT CHARACTER SET ', DEFAULT_CHARACTER_SET_NAME, ';' ) AS CreateDatabaseQueryFROM information_schema.SCHEMATAWHERE SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' ); |
5.拼接创建用户的语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SELECT CONCAT( 'create user \'', user, '\'@\'', Host, '\'' ' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQueryFROM mysql.`user`WHERE `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );#有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户 |
6.导出权限脚本 这个shell脚本也用到了拼接
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#!/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.sqlecho "flush privileges;" >> /tmp/grants.sql |
7.查找表碎片
|
1
2
3
4
5
6
7
8
|
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 datafreeFROM information_schema.tables tWHERE t.TABLE_SCHEMA = 'test' order by DATA_LENGTH desc; |
8.查找无主键表 这个没用到拼接 也分享出来吧
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
#查找某一个库无主键表SELECTtable_schema,table_nameFROM information_schema.TABLESWHERE 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_nameFROM information_schema. TABLES t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHERE t2.table_name IS NULLAND t1.TABLE_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys') ; |
以上就是MySQL 常用的拼接语句汇总的详细内容，更多关于MySQL 拼接语句的资料请关注服务器之家其它相关文章！
原文链接：https://cloud.tencent.com/developer/article/1500400
