欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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 拼接语句的资料请关注其它相关文章!