当前位置:   article > 正文

批量修改表字段排序规则_批量修改表的排序规则

批量修改表的排序规则

修改字段排序规则

SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表',
    COLUMN_NAME AS '字段',
    CHARACTER_SET_NAME AS '原字符集',
    COLLATION_NAME AS '原排序规则',
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS '修正SQL'
FROM
    information_schema.`COLUMNS`
WHERE
    CHARACTER_SET_NAME = 'utf8mb4' AND COLLATION_NAME != 'utf8mb4_0900_ai_ci' AND TABLE_SCHEMA = '数据库名称';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

修改表字段默认规则

SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表',
    TABLE_COLLATION AS '原排序规则',
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS '修正SQL'
FROM
    information_schema.`TABLES`
WHERE
    TABLE_COLLATION IN ('utf8_general_ci','utf8mb4_general_ci') AND TABLE_SCHEMA LIKE '数据库名称%';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/笔触狂放9/article/detail/750476
推荐阅读
相关标签
  

闽ICP备14008679号