MySQL按主键排序 [英] MySQL order by primary key

问题描述

某些SQL Server允许使用通用语句，例如ORDER BY PRIMARY KEY.我认为这不适用于MySQL，是否有任何这样的解决方法可以跨多个表进行自动选择，还是需要使用查询查询来确定主键?

Some SQL servers allow for a generic statement such as ORDER BY PRIMARY KEY. I don't believe this works for MySQL, is there any such workaround that would allow for automated selects across multiple tables or does it require a lookup query to determine the primary key?

我一直在努力的解决方法是在运行查询之前调用SHOW COLUMNS FROM.有更有效的方法吗?在选择过程中，MySQL可以确定表的主键吗?

The workaround I have been working on involves calling a SHOW COLUMNS FROM before running the query. Is there a more efficient way of doing this? Can MySQL determine the primary key of a table during the select process?

更新:正如Gordon所指出的那样，在MySQL或SQL中一般没有官方的方法. SAP具有自定义功能.有一些变通办法，例如像John指出的那样使用SHOW COLUMNS FROM table或information_schema.

Update: There is no official way of doing this in MySQL or SQL in general as Gordon pointed out. SAP has custom functionality for it. There are workarounds, such as working with SHOW COLUMNS FROM table or the information_schema as John pointed out.

推荐答案

MySQL通常按插入顺序将数据从主键中拉出，但从技术上讲，如果您拉出主键列，您在技术上也可以做同样的事情命名并按以下顺序排列

MySQL generally pulls data out by insertion order which would be by primary key, but that aside you technically can do the same thing if you pull out the primary key column name and put it in an order by

SELECT whatever FROM table
ORDER BY
(   SELECT `COLUMN_NAME`
    FROM `information_schema`.`COLUMNS`
    WHERE (`TABLE_SCHEMA` = 'dbName')
      AND (`TABLE_NAME` = 'tableName')
      AND (`COLUMN_KEY` = 'PRI')
);

对于复合键，您可以使用此

For composite keys you can use this

SELECT whatever FROM table
ORDER BY
(   SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ', ')
    FROM `information_schema`.`COLUMNS`
    WHERE (`TABLE_SCHEMA` = 'dbName')
      AND (`TABLE_NAME` = 'tableName')
      AND (`COLUMN_KEY` = 'PRI')
);

DOCS

每个MySQL用户都有权访问这些表，但是 只能看到表中与对象相对应的行 用户具有适当的访问权限.在某些情况下(对于 例如， INFORMATION_SCHEMA.ROUTINES表)，用户数量不足 权限请参见NULL.这些限制不适用于InnoDB 桌子;您只能以PROCESS权限查看它们.

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges see NULL. These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege.

相同的特权适用于从中选择信息 INFORMATION_SCHEMA并通过SHOW查看相同的信息 陈述.无论哪种情况，您都必须对某个对象具有一定的特权 查看有关它的信息.

The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements. In either case, you must have some privilege on an object to see information about it.

设置:

CREATE TABLE some_stuff (
    firstID INT,
    secondID INT,
    username varchar(55),
    PRIMARY KEY (firstID, secondID)
) ;

查询:

SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ', ')
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'dbName')
  AND (`TABLE_NAME` = 'some_stuff')
  AND (`COLUMN_KEY` = 'PRI');

输出:

+--------------------------------------------+
| GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ', ') |
+--------------------------------------------+
|              firstID, secondID             |
+--------------------------------------------+

这篇关于MySQL按主键排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！