MySql8一些小技巧

121 阅读1分钟

#1 批量修改字段的collation

SELECT
    CONCAT(
        'ALTER TABLE `',
        TABLE_NAME,
        '` MODIFY `',
        COLUMN_NAME,
        '` ',
        DATA_TYPE,
        '(',
        CHARACTER_MAXIMUM_LENGTH,
        ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
        ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
        ';' 
) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_SCHEMA = '数据库名' 
    AND (
    DATA_TYPE = 'varchar' 
    OR DATA_TYPE = 'char')

## 方式二

SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段', DATA_TYPE '字段类型',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') '修正SQL' FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='gqgs' and COLLATION_NAME RLIKE 'utf8mb4_0900_ai_ci' ;

#2 批量修改表的collation

SELECT
    CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = '数据库名';
    
## 方式二
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_unicode_ci;') '修正SQL'
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA='gqgs' and TABLE_COLLATION RLIKE 'utf8mb4_0900_ai_ci';

#3 批量修改数据库的collation

SELECT SCHEMA_NAME '数据库',DEFAULT_CHARACTER_SET_NAME '原字符集',DEFAULT_COLLATION_NAME '原排序规则',CONCAT('ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') '修正SQL'
FROM information_schema.`SCHEMATA`
WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8';

#4 MySQL 创建函数报错 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

## 临时方案
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;

## 永久方案
#在 my.cnf 配置文件中添加如下一行配置,然后重启MySQL服务
# /etc/mysql$ sudo vim mysql.conf.d/mysqld.cnf
log_bin_trust_function_creators = 1

#5 Mysql中varchar存放中文与英文所占字节异同

  • UTF-8:一个汉字 = 3个字节varchar(3),英文是一个字节varchar(1)
  • GBK: 一个汉字 = 2个字节,英文是一个字节