转 【MySQL】常用拼接语句 shell 下执行mysql 命令

 

 

【MySQL】常用拼接语句

前言：在MySQL中 CONCAT ()函数用于将多个字符串连接成一个字符串，利用此函数我们可以将原来一步无法得到的sql拼接出来，在工作中也许会方便很多，下面主要介绍下几个常用的场景。

注：适用于5.7版本 低版本可能稍许不同。

1.拼接查询所有用户

mysql">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'
) ;

#########PS:

mysql 查询后不显示标题
2018年10月22日 15:48:00 weixin_33872566 阅读数 2
mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb

直接进入命令行，select，是有|显示的格式的。

mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb<1.sql

这样使用批处理的方式执行，显示是没有|的。

mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb -t<1.sql

如果增加了-t命令项，则会显示|格式

如果使用-vvv选项，则会回显命令。

mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb -N<1.sql

如果使用-N选项，则不显示标题。

在mysql命令行下，可以直接执行批处理文件。

mysql>. 1.sql
作者：mydriverc2
来源：CSDN
原文：https://blog.csdn.net/mydriverc2/article/details/38895395

 

 

###3

https://www.cnblogs.com/wangkangluo1/archive/2012/04/27/2472898.html

shell 下执行mysql 命令

 

原文链接

在shell开发中，很多时候我们需要操作mysql数据库（比如：查询数据、导出数据等），但是我们又无法进入mysql命令行的环境，就需要在shell环境中模拟mysql的环境，使用mysql相关命令，本文总结几种shell操作mysql的方法，供大家参考。

方案1

mysql -uuser -ppasswd -e"insert LogTable values(...)"

 

优点：语句简单

缺点：支持的sql相对简单

 

方案2

准备一个sql脚本，名字为update.sql，例如：

CREATE TABLE `user` (
  `id` varchar(36) NOT NULL COMMENT '主键',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(50) NOT NULL COMMENT '用户密码',
  `createdate` date NOT NULL COMMENT '创建时间',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';
DROP TABLE IF EXISTS `visit_log`;
CREATE TABLE `visit_log` (
  `id` varchar(36) character set utf8 NOT NULL,
  `type` int(11) NOT NULL,
  `content` text character set utf8 NOT NULL,
  `createdate` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='访问日志';

 

 新建一个update_mysql.sh，内容如下：

use chbdb;
source update.sql

 

 

然后执行如下命令：

cat update_mysql.sh | mysql --user=root -ppassword

 

优点：支持复杂的sql脚本

缺点：

1> 需要两个文件：update.sql和update_mysql.sh

2> 一旦中间出错，之后脚本就不会执行，例如：

如果第一张表已经存在，则会报出如下异常：

ERROR 1050 (42S01) at line 1 in file: 'update.sql': Table 'user' already exists

然后脚本退出，第二张表也就无法创建。

方案3

    新建一个shell脚本，格式如下：

#!/bin/bash
mysql -u* -h* -p* <<EOF
    Your SQL script.
EOF

 

例如：

#!/bin/bash
mysql -uroot  -ppassword <<EOF
   use chbdb;
    CREATE TABLE user (
  id varchar(36) NOT NULL COMMENT '主键',
  username varchar(50) NOT NULL COMMENT '用户名',
  password varchar(50) NOT NULL COMMENT '用户密码',
  createdate date NOT NULL COMMENT '创建时间',
  age int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';

 

优点：

1>支持复杂的sql脚本

2>无需其它额外文件

缺点：

1> 表名、字段不能使用单引号，需要修改原有sql语句

2> 一旦中间出错，之后脚本就不会执行，例如：

如果第一张表已经存在，则会报出如下异常：

ERROR 1050 (42S01) at line 1 in file: 'update.sql': Table 'user' already exists

然后脚本退出，第二张表也就无法创建。

方案4

准备一个sql脚本，如update.sql，然后执行如下命令：

mysql -uroot -ppassword < update.sql

 

优点：支持复杂的sql脚本

缺点：

1> 一旦中间出错，之后脚本就不会执行，例如：

如果第一张表已经存在，则会报出如下异常：

ERROR 1050 (42S01) at line 1 in file: 'update.sql': Table 'user' already exists

然后脚本退出，第二张表也就无法创建。

 

 

    大家知道在mysql命令行中使用source命令，即使中间出错，后续脚本也会继续执行，但是如上几种方式，均无法解决该问题，如果大家有好的建议，请回复，谢谢。