MySQL 常用的拼接语句汇总

作者: 乖宝宝v
来源: 51数据库
2021-07-07

前言：在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 拼接语句的资料请关注其它相关文章！