MySQL 常用的拼接语句汇总
程序员文章站
2022-07-11 14:26:46
前言:在mysql中 concat ()函数用于将多个字符串连接成一个字符串,利用此函数我们可以将原来一步无法得到的sql拼接出来,在工作中也许会方便很多,下面主要介绍下几个常用的场景。注:适用于5....
前言:在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 拼接语句的资料请关注其它相关文章!