Asked  7 Months ago    Answers:  5   Viewed   33 times

I'm, generating a SQL query like this in PHP:

$sql = sprintf("UPDATE %s SET %s = %s WHERE %s = %s", ...);

Since almost every part of this query is dynamic I need a way to determine the table's primary key dynamically, so that I'd have a query like this:

$sql = sprintf("UPDATE %s SET %s=%s WHERE PRIMARY_KEY = %s", ...);

Is there a MySQL keyword for a table's primary key, or a way to get it?

I've used the information_schema DB before to find information like this, but it'd be nice if I didn't have to resort to that.

 Answers

65
SHOW INDEX FROM <tablename>

You want the row where Key_name = PRIMARY

http://dev.mysql.com/doc/refman/5.0/en/show-index.html

You'll probably want to cache the results -- it takes a while to run SHOW statements on all the tables you might need to work with.

Wednesday, March 31, 2021
 
THEK
answered 7 Months ago
48

A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction.

A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no two x and y where x is not y and x == y).

The manual explains it as follows:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

A primary key is a 'special' unique key. It basically is a unique key, except that it's used to identify something.

The manual explains how indexes are used in general: here.

In MSSQL, the concepts are similar. There are indexes, unique constraints and primary keys.

Untested, but I believe the MSSQL equivalent is:

CREATE TABLE tmp (
  id int NOT NULL PRIMARY KEY IDENTITY,
  uid varchar(255) NOT NULL CONSTRAINT uid_unique UNIQUE,
  name varchar(255) NOT NULL,
  tag int NOT NULL DEFAULT 0,
  description varchar(255),
);

CREATE INDEX idx_name ON tmp (name);
CREATE INDEX idx_tag ON tmp (tag);

Edit: the code above is tested to be correct; however, I suspect that there's a much better syntax for doing it. Been a while since I've used SQL server, and apparently I've forgotten quite a bit :).

Monday, June 28, 2021
 
cegfault
answered 4 Months ago
64
  1. You can't remove embedded index since it's the only address of row.
  2. Merge your 2 integer keys in single long key = (key1<<32) + key2; and make this as a INTEGER PRIMARY KEY in youd schema (in that case you will have only 1 index)
  3. Set page size for new DB at least 4096
  4. Remove ANY additional index except primary
  5. Fill in data in the SORTED order so that primary key is growing.
  6. Reuse commands, don't create each time them from string
  7. Set page cache size to as much memory as you have left (remember that cache size is in number of pages, but not number of bytes)
  8. Commit every 50000 items.
  9. If you have additional indexes - create them only AFTER ALL data is in table

If you'll be able to merge key (I think you're using 32bit, while sqlite using 64bit, so it's possible) and fill data in sorted order I bet you will fill in your first Gb with the same performance as second and both will be fast enough.

Monday, July 19, 2021
 
Jimenemex
answered 3 Months ago
53

It's the same as MySQL ignores the CONSTRAINT pk_PersonID part. You can check by creating the table and then dumping it or issuing SHOW CREATE TABLE Persons.

I guess it supports this syntax only for compatibility with other SQL servers (but ignores it for primary and other local keys) and does not store its information (the constraint name).

However for usage with foreign keys the CONSTRAINT keyword is used also in MySQL.

mysql> CREATE TABLE test.Persons (
    -> P_Id int NOT NULL,
    -> LastName varchar(255) NOT NULL,
    -> FirstName varchar(255),
    -> Address varchar(255),
    -> City varchar(255),
    -> CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
    -> );
Query OK, 0 rows affected (0.50 sec)

server$ mysqldump -p test Persons
Enter password:
--
-- Table structure for table `Persons`
--
DROP TABLE IF EXISTS `Persons`;
CREATE TABLE `Persons` (
  `P_Id` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`P_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is also test to prove MySQL doesn't store the constraint name anywhere and doesn't use it when printing errors (as mentioned for other SQL servers in question What is the purpose of constraint naming :

mysql> insert into Persons (P_Id) values(1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Persons (P_Id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
Monday, August 23, 2021
 
some_bloody_fool
answered 2 Months ago
39

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.

SETUP:

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

QUERY:

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

OUTPUT:

+--------------------------------------------+
| GROUP_CONCAT(`COLUMN_NAME` SEPARATOR ', ') |
+--------------------------------------------+
|              firstID, secondID             |
+--------------------------------------------+
Monday, September 20, 2021
 
Jeffrey Stilwell
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :