MySQL大小写敏感表迁移PostgreSQL遇阻，如何查询区分大小写的约束？

问题场景 #

在将MySQL数据库迁移至PostgreSQL的过程中，现有迁移程序执行失败，原因是MySQL中存在仅大小写不同的两张独立表，例如：

select * from `Adatlap`
select * from `adatlap`

通过information_schema查询数据库结构时无法区分这两张表，因为information_schema中的约束、列名等标识符均以全小写形式呈现。执行以下查询：

SELECT t.constraint_type, t.table_schema, t.table_name, k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
  AND t.table_schema='main'
  AND t.table_name='adatlap'   

返回结果会将两张表的主键信息混在一起：

constraint_type|table_schema  |table_name|column_name|
---------------+--------------+----------+-----------+
PRIMARY KEY    |main          |adatlap   |id         |
PRIMARY KEY    |main          |adatlap   |id         |

需要找到能筛选出区分大小写的约束与列名的方法，或替代的大小写敏感系统目录。

解决方法 #

1. 直接查询MySQL底层系统表 #

information_schema是MySQL的视图层，会根据配置对标识符做大小写转换，无法保留原始大小写信息。要获取准确的区分大小写的表、约束和列信息，需直接查询MySQL的底层系统表：

查询区分大小写的表名

SELECT table_name FROM mysql.tables 
WHERE table_schema = 'main' AND BINARY table_name IN ('Adatlap', 'adatlap');

使用BINARY关键字强制字符串比较时区分大小写，确保能匹配到原始大小写的表名。

查询区分大小写的主键约束及列

SELECT 
  tc.constraint_type,
  tc.table_schema,
  t.table_name AS original_table_name,
  k.column_name
FROM mysql.table_constraints tc
JOIN mysql.tables t 
  ON tc.table_schema = t.table_schema AND tc.table_name = t.table_name
JOIN mysql.key_column_usage k 
  USING(constraint_name, table_schema, table_name)
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_schema = 'main'
  AND BINARY t.table_name = 'Adatlap'; -- 替换为目标表名

通过直接访问mysql.tables、mysql.table_constraints、mysql.key_column_usage这些底层系统表，配合BINARY做大小写敏感匹配，即可获取对应表的准确约束和列信息。

2. 检查lower_case_table_names配置 #

MySQL的lower_case_table_names配置会影响表名的存储和查询行为，可通过以下命令查看当前设置：

SHOW VARIABLES LIKE 'lower_case_table_names';

• 值为0：表名存储和查询均区分大小写；
• 值为1：表名存储为小写，查询时自动转换为小写；
• 值为2：表名存储保留原始大小写，查询时转换为小写。
  无论配置值是多少，底层系统表都会保留表名的原始大小写，因此上述底层表查询方法始终有效。

3. PostgreSQL迁移适配 #

PostgreSQL默认对标识符（表名、列名）大小写不敏感，除非创建时用双引号包裹。迁移这类区分大小写的MySQL表时需注意：

• 在PostgreSQL中创建表时，用双引号包裹表名，例如：CREATE TABLE "Adatlap" (id INT PRIMARY KEY, ...);
• 迁移数据和约束时，确保所有标识符的大小写与MySQL完全一致，避免出现混淆或冲突。

内容的提问来源于stack exchange，提问作者nagylzs