MySql业务场景处理

1. 批量插入

将一个表删除的数据插入到另一个表（备份）

insert into table_name_back select *  from table_name a where a.id= ?;
delete from table_name a where a.id= ? ;

从一个表获取数据，向另一个表插入数据，能够解决数据的重复问题

insert INTO test_demo (id,name)
select distinct table_id,id from act_z_business -- 要插入的数据集
on duplicate key update create_by = 'test';

2. 查询重复数据

查询表中某一个字段的值相同的数据集

select id,`name`,count(1) as c
from TABLE_NAME GROUP BY `name` HAVING c> 1

3. 批量修改Mysql表的字符集

SELECT
	CONCAT(
		'ALTER TABLE ',
		TABLE_NAME,
		' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
	)
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'mysql'; // TABLE_SCHEMA = 'mysql'

4. 批量修改Mysql表字段的字符集

SELECT
  TABLE_SCHEMA '数据库',
  TABLE_NAME '表',
  COLUMN_NAME '字段',
  CHARACTER_SET_NAME '原字符集',
  COLLATION_NAME '原排序规则',
  CONCAT( 'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL' 
FROM
  information_schema.`COLUMNS` 
WHERE
  TABLE_SCHEMA RLIKE 'DATABASE_NAME'
  and CHARACTER_SET_NAME is not null 
  and (CHARACTER_SET_NAME != 'utf8mb4' or COLLATION_NAME != 'utf8mb4_general_ci');

5.查询表所有字段

select column_name from information_schema.columns where table_schema='DATABASE_NAME' and table_name='TABLE_NAME';

6.向表添加字段

在字段
field之后添加 IDS 字段

ALTER TABLE TABLE_NAME ADD COLUMN IDS int(11) DEFAULT NULL COMMENT '主键' AFTER field; 

7.重新分析表索引

ANALYZE table test table_name;
ANALYZE table test_11;

8.给角色加权限

CREATE USER canal IDENTIFIED BY 'root'; 
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

9.sql_model修改

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

10.添加虚拟列(使用索引)

# 1. 根据实体列的值创建一个虚拟列
ALTER TABLE `table_name` ;
ADD COLUMN `field_name` CHAR(1) GENERATED ALWAYS AS (SUBSTR(projid, 16, 1)) STORED;
# 2. 添加虚拟列索引
CREATE INDEX idx_field_name ON 表名(field_name);
# 3. 查询索引
SELECT * FROM 表名 WHERE field_name = 'A';

11.查询锁表(block)进程并停止

select * from information_schema.INNODB_TRX;
KILL 30009;