写在前面
这里总结下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即可…
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
