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?
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?
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.
On a query on one table with a primary key, no explicit order by , and no where conditions, MySQL will generally return the results in primary key order.
Order by Primary Key, does not sort the results, it fetches the data sorted as they are sorted on the database according to the primary key, if the database is sorting the table, if not, the results are sorted according to the primary keys.
In SQL Server: no, by it's clustering key - which default to the primary key, but doesn't have to be the same. The primary key's main function is to uniquely identify each row in the table - but it doesn't imply any (physical) sorting per se.
Does the order of columns in a PK index matter? Yes it does. By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table.
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')
);
Permission for information schema access from the DOCS
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.
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 |
+--------------------------------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With