不同数据库标识长度检查

以下是主流数据库标识符长度检查

oracle

-- 检查表名是否过长
SELECT table_name, LENGTH(table_name) AS name_length
FROM user_tables
WHERE LENGTH(table_name) > 30;

-- 检查列名是否过长
SELECT table_name, column_name, LENGTH(column_name) AS name_length
FROM user_tab_columns
WHERE LENGTH(column_name) > 30;

-- 检查索引名是否过长
SELECT index_name, table_name, LENGTH(index_name) AS name_length
FROM user_indexes
WHERE LENGTH(index_name) > 30;

-- 检查触发器名是否过长
SELECT trigger_name, table_name, LENGTH(trigger_name) AS name_length
FROM user_triggers
WHERE LENGTH(trigger_name) > 30;

-- 检查同义词名是否过长 
SELECT synonym_name, table_name, LENGTH(synonym_name) AS name_length FROM user_synonyms WHERE LENGTH(synonym_name) > 30; 
-- 检查视图名是否过长 
SELECT view_name, LENGTH(view_name) AS name_length FROM user_views WHERE LENGTH(view_name) > 30;

mysql

-- 替换为实际数据库名
SET @db_name = '你的数据库名';

-- 检查表名是否过长
SELECT table_name, LENGTH(table_name) AS name_length
FROM information_schema.tables
WHERE table_schema = @db_name 
  AND LENGTH(table_name) > 64;

-- 检查列名是否过长
SELECT table_name, column_name, LENGTH(column_name) AS name_length
FROM information_schema.columns
WHERE table_schema = @db_name 
  AND LENGTH(column_name) > 64;

-- 检查索引名是否过长
SELECT 
  table_name, 
  index_name, 
  LENGTH(index_name) AS name_length
FROM information_schema.statistics
WHERE table_schema = @db_name
  AND LENGTH(index_name) > 64;

-- 检查触发器名是否过长
SELECT 
  trigger_name,
  event_object_table AS table_name,
  LENGTH(trigger_name) AS name_length
FROM information_schema.triggers
WHERE trigger_schema = @db_name
  AND LENGTH(trigger_name) > 64;

mssql

-- 检查表名是否过长
SELECT name AS table_name, LEN(name) AS name_length
FROM sys.tables
WHERE LEN(name) > 128;

-- 检查列名是否过长
SELECT 
  t.name AS table_name, 
  c.name AS column_name, 
  LEN(c.name) AS name_length
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE LEN(c.name) > 128;

-- 检查索引名是否过长
SELECT 
  t.name AS table_name,
  i.name AS index_name,
  LEN(i.name) AS name_length
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE LEN(i.name) > 128
  AND i.type_desc <> 'HEAP';

-- 检查触发器名是否过长
SELECT 
  t.name AS table_name,
  tr.name AS trigger_name,
  LEN(tr.name) AS name_length
FROM sys.triggers tr
JOIN sys.tables t ON tr.parent_id = t.object_id
WHERE LEN(tr.name) > 128;

pgsql

-- 检查表名是否过长
SELECT table_name, LENGTH(table_name) AS name_length
FROM information_schema.tables
WHERE table_schema = 'public'  -- 通常默认 schema
  AND LENGTH(table_name) > 63;

-- 检查列名是否过长
SELECT table_name, column_name, LENGTH(column_name) AS name_length
FROM information_schema.columns
WHERE table_schema = 'public'
  AND LENGTH(column_name) > 63;

-- 检查索引名是否过长
SELECT 
  t.relname AS table_name,
  i.relname AS index_name,
  LENGTH(i.relname) AS name_length
FROM pg_class i
JOIN pg_index ix ON i.oid = ix.indexrelid
JOIN pg_class t ON ix.indrelid = t.oid
WHERE i.relkind = 'i'  -- 只看索引
  AND LENGTH(i.relname) > 63;

-- 检查触发器名是否过长
SELECT 
  t.relname AS table_name,
  trg.tgname AS trigger_name,
  LENGTH(trg.tgname) AS name_length
FROM pg_trigger trg
JOIN pg_class t ON trg.tgrelid = t.oid
WHERE NOT trg.tgisinternal  -- 排除内部触发器
  AND LENGTH(trg.tgname) > 63;

sqlite

-- 检查表名是否过长
SELECT name AS table_name, LENGTH(name) AS name_length
FROM sqlite_master
WHERE type = 'table'
  AND LENGTH(name) > 64;

-- 检查列名是否过长（需要替换为实际表名）
-- 方法1：查询特定表的列
PRAGMA table_info('你的表名');
-- 然后在结果中筛选 name 字段长度 > 64 的列

-- 方法2：通过 sqlite_master 结合 PRAGMA 遍历（需在客户端脚本中实现）
-- 以下是示例思路，实际需程序循环执行
SELECT name AS table_name FROM sqlite_master WHERE type = 'table';
-- 对每个表执行 PRAGMA table_info(table_name) 并检查列名长度

-- 检查索引名是否过长
SELECT name AS index_name, tbl_name AS table_name, LENGTH(name) AS name_length
FROM sqlite_master
WHERE type = 'index'
  AND LENGTH(name) > 64;

-- 检查触发器名是否过长
SELECT name AS trigger_name, tbl_name AS table_name, LENGTH(name) AS name_length
FROM sqlite_master
WHERE type = 'trigger'
  AND LENGTH(name) > 64;