具有未知列名的 MySQL SELECT

Question

我需要这样的查询:SELECT id FROM database.table;但是我没有主键的列名，因为查询必须对更多表具有通用性。所以我试着这样查询:

SELECT (SELECT k.COLUMN_NAME AS pk_old
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
    AND t.table_schema='database'
    AND t.table_name='table')as pk FROM database.table;

但是我得到的不是带有该表主键值的列表，例如 1,2,3,4...，而是带有 id,id,id,id... 的列表。如何获取主键的值？

Answer 1

使用prepare语句获取

生成查询

SELECT
  CONCAT( 'SELECT ',
  GROUP_CONCAT(CONCAT(COLUMN_NAME,' AS FIELD_',ORDINAL_POSITION)) ,
  ' FROM ', TABLE_SCHEMA,'.',TABLE_NAME,' ORDER BY ',
  GROUP_CONCAT(COLUMN_NAME)) INTO @query
FROM information_schema.KEY_COLUMN_USAGE

WHERE
  TABLE_SCHEMA = 'YourSchema'
AND
  TABLE_NAME = 'YorTable'
AND 
  CONSTRAINT_NAME='PRIMARY'
ORDER BY ORDINAL_POSITION;

验证一下(仅供测试)

SELECT @query;

执行准备语句

PREPARE stmt FROM @query;
EXECUTE stmt;

放弃它

DEALLOCATE PREPARE stmt;