【MySQL】备忘录

导出数据库所有的索引

SELECT
	CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'ADD ',
		IF (
			NON_UNIQUE = 1,
			CASE 
				UPPER( INDEX_TYPE ) 
				WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX' 
				WHEN 'SPATIAL' THEN 'SPATIAL INDEX' 
				ELSE CONCAT( 'INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE ) 
			END,
			IF ( 
				UPPER( INDEX_NAME ) = 'PRIMARY', 
				CONCAT( 'PRIMARY KEY USING ', INDEX_TYPE ), 
				CONCAT( 'UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE ) 
			) 
		),
		'(',
		GROUP_CONCAT( DISTINCT CONCAT( '`', COLUMN_NAME, '`' ) ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', ' ),
		');' 
	) AS 'Show_Add_Indexes' 
FROM
	information_schema.STATISTICS 
WHERE
	TABLE_SCHEMA = 'fle_dev' 
GROUP BY
	TABLE_NAME,
	INDEX_NAME 
ORDER BY
	TABLE_NAME ASC,
	INDEX_NAME ASC

导出数据库所有自增

SELECT
	CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY COLUMN `', COLUMN_NAME, '` ',
		IF (
			UPPER( DATA_TYPE ) = 'INT',
			REPLACE ( SUBSTRING_INDEX( UPPER( COLUMN_TYPE ), ')', 1 ), 'INT', 'INTEGER' ),
			UPPER( COLUMN_TYPE ) 
		),
		') UNSIGNED NOT NULL AUTO_INCREMENT;' 
	) AS 'Show_Add_AUTO_INCREMENT' 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'fle_dev' 
	AND EXTRA = UPPER( 'AUTO_INCREMENT' ) 
ORDER BY
	TABLE_NAME ASC

新建表

CREATE TABLE `big_customer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NULL COMMENT '创建时间',
  `update_time` datetime NULL COMMENT '修改时间',
  `ding_dept_id` varchar(50) NOT NULL DEFAULT '' COMMENT '钉钉部门ID',
  `count_mon` varchar(7) NOT NULL DEFAULT '' COMMENT '统计日期',
  `report_name` varchar(100) NOT NULL DEFAULT '' COMMENT '显示名称',
  `report_sort` tinyint(2) NULL COMMENT '显示顺序',
  `budget_m` decimal(18,2) NULL COMMENT '月预算/目标',
  `profit_m` decimal(18,2) NULL COMMENT '月实际结果',
  `ratio_m` decimal(10,4) NULL COMMENT '月占比',
  `ratio_mm` decimal(10,4) NULL COMMENT '月同比',
  `ratio_mf` decimal(10,4) NULL COMMENT '月完成率',
  `budget_y` decimal(18,2) NULL COMMENT '年预算/目标',
  `profit_y` decimal(18,2) NULL COMMENT '年实际结果',
  `ratio_y` decimal(10,4) NULL COMMENT '年占比',
  `ratio_yy` decimal(10,4) NULL COMMENT '年同比',
  `ratio_yf` decimal(10,4) NULL COMMENT '年完成率',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_ding_dept_id_count_mon_report_name`(`ding_dept_id`,`count_mon`,`report_name`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic COMMENT='大客户数据表单';

新增列

ALTER TABLE bus_payroll_record ADD demand_category_id VARCHAR(40) COMMENT '任务行业类型Id'

备份表

CREATE TABLE medicine_param_bak LIKE medicine_param;
INSERT INTO medicine_param_bak SELECT * FROM medicine_param;

数据库备份脚本

#!/bin/bash
# -------------------------------------------------------------------------------
# FileName: uker_psp_backup.sh
# Describe: Used for uker_psp database backup
# Revision: 1.0
# Date: 2021/03/14
# Author: daijiong
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="SuccessHR@2021"
mysql_host="localhost"
mysql_port="3306"
backup_dir=/data/mysql_backup
backup_database="uker_psp"
reservation_day="1"
dt=$(date +"%Y%m%d")
echo "Backup Begin Date:" $(date +"%Y-%m-%d %H:%M:%S")
# 备份uker_psp数据库
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $backup_database > $backup_dir/$backup_database/$dt.sql
find $backup_dir/$backup_database -mtime +$reservation_day -type f -name '*.sql' -exec rm -rf {} \;
echo "Backup Succeed Date:" $(date +"%Y-%m-%d %H:%M:%S")

MySQL命令连接服务器

mysql -u root -p -P 13306 --socket=/home/kettle-pack/mysql/tmp/mysql.sock

查看连接MySQL服务器的会话

SHOW FULL PROCESSLIST;

查看MySQL服务器的表锁

SHOW ENGINE INNODB STATUS;

此命令只针对MySQL是INNODB引擎的