Mysql中常用的高级脚本，批量等操作（脚本维护，数据导入/导出/清洗/迁移,备份等等操作），持续更

写在前面

这里总结下Mysql中积累的一些批量操作

Mysql 系统中内置了两个很重要的数据库Mysql、information_schema、sys 还有performance_schema，后续我们自创的DB都在这四个表里有相应维护

​​这里有关于这四个数据库的更详细的介绍​​

这里的脚本总结，也差不多都是基于这四个系统内置数据库中的相关维护，批量操作我们的业务数据库

包括以下

一、批量更改数据库的字符集（数据库、表，字段等）

1.1、修改数据库字符集

-- 可直接复制运行
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin; 
-- 参数解释：
    -- db_name 数据库名；
    -- utf8mb4 改后的字符集
    -- utf8mb4_bin 改后的排序规则

1.2、批量修改某一数据库中，所有的表和表中的字段的编码和排序规则

– 第一步，先生成修改的SQL语句

USE testboot;
SELECT
    CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME,
' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) 
FROM
    information_schema.`COLUMNS` 
WHERE
    COLLATION_NAME RLIKE 'utf8mb4_general_ci' 
    AND TABLE_SCHEMA = 'testboot';

-- 参数解释
    -- testboot 你要修改的数据库
    -- utf8mb4 改后的编码
    -- 第一个 utf8mb4_general_ci 改后的排序规则
    -- 第二个 utf8mb4_general_ci 要改的表的现在的排序规则，这里本来是utf8mb4_bin,我改过的，用utf8mb4_bin，会返回空
    -- testboot 你要修改的数据库

类似这种

ALTER TABLE testboot.address MODIFY COLUMN address varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.address MODIFY COLUMN city varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.address MODIFY COLUMN province varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.blog MODIFY COLUMN content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.blog MODIFY COLUMN description varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.blog MODIFY COLUMN summary varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.blog MODIFY COLUMN tags varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.blog MODIFY COLUMN title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.navigation MODIFY COLUMN name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.navigation MODIFY COLUMN path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.user MODIFY COLUMN email varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.user MODIFY COLUMN name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.user MODIFY COLUMN password varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE testboot.user MODIFY COLUMN tel varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

第二步，复制上述生成sql语句，在控制台里执行就可以了

注意这里会初始化字段的注释，字段长度，只供参考

修改完毕；可查看。

二、批量添加注释

2.1、批量添加表注释

SELECT distinct CONCAT(
                        'ALTER TABLE ', TABLE_NAME,
                        ' COMMENT ', ';') as tname
FROM information_schema.`COLUMNS`
where TABLE_SCHEMA = 'pis-test'
  and TABLE_NAME = 'pis_user';

可得到如下，具体注释值，就需要你再补充了

ALTER TABLE pis_absence COMMENT '';
ALTER TABLE pis_clock COMMENT '';
ALTER TABLE pis_config COMMENT '';
ALTER TABLE pis_contract COMMENT '';
ALTER TABLE pis_department COMMENT '';
ALTER TABLE pis_dictionary COMMENT '';
ALTER TABLE pis_finance COMMENT '';
ALTER TABLE pis_group COMMENT '';
ALTER TABLE pis_leave_msg COMMENT '';

2.2、批量添加表字段注释

表注释完成后，就需要添加表中字段的注释了，这个就稍微麻烦一点，因为我们只是要更新添加字段注释，又要小心因 alter 语句引起的表结构的变化，这个要特别注意下！！

这里我是区分每一个表操作的（每一个表，整理出一份注释alter脚本），示例其中一个表

方式一

show create table pis_config;

得到如下,我们Copy出需要修改的部分？？这里需要注意一下！Mysql对field修改时，是需要完整的字段定义的（）

CREATE TABLE `pis_config` (
  `id_` bigint(20) NOT NULL AUTO_INCREMENT ,
  `create_by` varchar(255) DEFAULT NULL ,
  `create_time` datetime DEFAULT NULL ,
  `key_` varchar(255) DEFAULT NULL ,
  `summary_` varchar(255) DEFAULT NULL ,
  `update_by` varchar(255) DEFAULT NULL ,
  `update_time` datetime DEFAULT NULL ,
  `value_` varchar(255) DEFAULT NULL ,
  PRIMARY KEY (`id_`),
  UNIQUE KEY `key_` (`key_`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8

Copy出之后，通过多行编辑器操作拼接如下

• ALTER TABLE pis_user MODIFY COLUMN user_id bigint(20) comment ‘ss’;

方式二

SELECT CONCAT(
               'ALTER TABLE ', TABLE_NAME,
               ' MODIFY COLUMN ', COLUMN_NAME,
               ' ', COLUMN_TYPE,
               ' comment ',  'zzz ',' ;')
FROM information_schema.`COLUMNS`
where TABLE_SCHEMA = 'pis'
  and TABLE_NAME = 'pis_user';

可获得如下

ALTER TABLE pis_user MODIFY COLUMN user_id bigint(20) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN assess_ int(11) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN company_ varchar(255) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN create_by varchar(255) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN create_time datetime comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN department_id bigint(20) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN department_name varchar(255) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN email_ varchar(255) comment  zzz  ;
ALTER TABLE pis_user MODIFY COLUMN entrance_status int(11) comment  zzz  ;
...
...

2.3、待完善的地方

这里用存储过程或者函数处理会更方便，待完善，后面我会抽空补全以下

-- 这里先记录一下，可供参考的shell,待写自定义函数处理批量表字段
SHOW COLUMNS FROM pis_user FROM `pis-test`;
SELECT table_name, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
# WHERE table_name = 'pis_user';
WHERE table_schema = 'pis-test';

SELECT table_name, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'pis-test';

SELECT CONCAT(
    'ALTER TABLE ', TABLE_NAME,
    ' MODIFY COLUMN ', COLUMN_NAME,
              ' ', COLUMN_TYPE)
FROM information_schema.`COLUMNS` where TABLE_SCHEMA = 'pis-test' and TABLE_NAME = 'pis_user';
show create table pis_user;

三、数据传输（复制、导入、导出）

这里只用 Mysql shell操作，如果可用 Navicate或其他连接客户端工具，可参考链接，这里有更便捷的操作方式,​​可供参考​​

3.1、数据导出 -mysqldump

命令行（mysql命令界面外）

-- 指定文件位置
mysqldump -uroot -p pis > d:\mydb.sqlpiss.sql;
mysqldump -u root -p mydatabase > /home/myuser/database-dump.sql

mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz] 
gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename] 

-- 备注年月日
mysqldump -u[username] -p[userpassword] --databases yourdatabase | gzip > /home/pi/database_backup/database_`date '+%m-%d-%Y'`.sql.gz

3.3、数据复制 – Select … into…

这里相当于中间表的使用

SELECT A.LastName,B.OrderNo
INTO AB
FROM A
INNER JOIN B
ON A.id=B.id

几个问题

这种操作会是同步的吗？还是基于当前语句的时间点复制数据 ？

3.2、数据导入

这里包括

五、数据备份/恢复

5.1、备份

这里有几个很重要的问题

备份的意义是什么，以及如何看待不同的备份？

如何保持数据的一致性？当我们从某个数据库备份数据时，确保里面的最后更新记录？和当前备份的一致性？

备份和导出的区别？

5.1、恢复

几个问题

备份和导入的区别？
要恢复什么数据 ？基于某个时间点 ？还是某个某种业务下的数据丢失问题 ？

六、数据库表比对

七、shell脚本

7.1、导出脚本

echo -e "Welcome to the import/export database utility\n"
echo -e "the default location of mysqldump file is: /opt/lampp/bin/mysqldump\n"
echo -e "the default location of mysql file is: /opt/lampp/bin/mysql\n"
read -p 'Would like you like to change the default location [y/n]: ' location_change
read -p "Please enter your username: " u_name
read -p 'Would you like to import or export a database: [import/export]: ' action
echo

mysqldump_location=/opt/lampp/bin/mysqldump
mysql_location=/opt/lampp/bin/mysql

if [ "$action" == "export" ]; then
    if [ "$location_change" == "y" ]; then
        read -p 'Give the location of mysqldump that you want to use: ' mysqldump_location
        echo
    else
        echo -e "Using default location of mysqldump\n"
    fi
    read -p 'Give the name of database in which you would like to export: ' db_name
    read -p 'Give the complete path of the .sql file in which you would like to export the database: ' sql_file
    $mysqldump_location -u $u_name -p $db_name > $sql_file
elif [ "$action" == "import" ]; then
    if [ "$location_change" == "y" ]; then
        read -p 'Give the location of mysql that you want to use: ' mysql_location
        echo
    else
        echo -e "Using default location of mysql\n"
    fi
    read -p 'Give the complete path of the .sql file you would like to import: ' sql_file
    read -p 'Give the name of database in which to import this file: ' db_name
    $mysql_location -u $u_name -p $db_name < $sql_file
else
    echo "please select a valid command"
fi

7.2、备份脚本

7.3、恢复数据脚本

八、数据库、表、字段导出Excel

8.1、导出SQL

SELECT
  a.table_comment 表说明,
  a.table_name 表名,

  b.COLUMN_NAME 字段名,
  b.column_comment 字段说明,
  b.column_type 字段类型,
  b.column_key 约束
FROM
  information_schema. TABLES a
    LEFT JOIN information_schema.COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
      -- 数据库名称
    a.table_schema = 'xxx'
ORDER BY
  a.table_name;

将结果复制到excel即可…

8.2、