MySQL中常用的拼接语句的小结(代码示例)

本篇文章给大家带来的内容是关于mysql中常用的拼接语句的小结(代码示例),有一定的参考价值,有需要的朋友可以参考一下,希望对你有所帮助。

前言:在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' ) ;

© 版权声明
THE END
喜欢就支持一下吧
点赞13 分享