Mysql Select record where PRIMARY key = x

问题:

I have a primary key in my mysql table which is comprised of three columns.

CREATE TABLE IF NOT EXISTS `bb_bulletin` (
  `OfficeCode` int(5) NOT NULL,
  `IssuerId` int(11) NOT NULL,
  `BulletinDtm` datetime NOT NULL,
  `CategoryCode` varchar(4) NOT NULL,
  `Title` varchar(255) NOT NULL,
  `Content` text NOT NULL,
  PRIMARY KEY (`OfficeCode`,`IssuerId`,`BulletinDtm`),
  UNIQUE KEY `U_IssuerId` (`IssuerId`,`OfficeCode`,`BulletinDtm`),
  UNIQUE KEY `U_CategoryCode` (`CategoryCode`,`OfficeCode`,`IssuerId`,`BulletinDtm`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Is there a shorthand method to select a record for a given value of the primary key.

I tried.

SELECT * FROM `bb_bulletin` WHERE PRIMARY = '20001-1-2011-01-07 14:04:40'

Instead of the long hand method of doing,

SELECT * From bb_bulletin WHERE OfficeCode = 20001 AND IssuerId = 1 AND BulletinDtm = 2011-01-07 14:04:40

What is the standard when dealing php and composite keys in your table. Note: I don't want to add autoincrementing keys to my tables in order to solve this. If it is not possible then I will just pass the three constraints in my url.

回答1:

I see two parts to your question. The first part is about referencing a composite value. I'm not sure whether MySQL support this, but it would be the SQL standard way of doing this:

SELECT * FROM bb_bulletin WHERE (OfficeCode, IssuerId, BulletinDtm) = (20001, 1, '2011-01-07 14:04:40');

The other part is referring to the primary key columns using an abbreviated syntax. I don't know of any such possibility.

回答2:

No, there is not such a way, either in MySQL, or in any SQL dialect I know.

You should split 20001-1-2011-01-07 14:04:40 string in your PHP and use its parts to build your MySQL query.

I might also add that, composite primary key might not be the best idea performance wise (especially with InnoDB tables)

Also INT(5) still takes just as much space as INT(11) (or plain INT for that matter). For smaller integer types use TINYINT, SMALLINT and MEDIUMINT

---

Clumsy Workarounds section

Solutions below should work the way you want, but at the cost of resources and/or performance. Unless you really can't go with the simplest solution, you should not use these.

---

A horrible way to do it would be like this WHERE CONCAT(OfficeCode,IssuerId,BulletinDtm) = '20001-1-2011-01-07 14:04:40'

It is HORRIBLE because it will not allow MySQL to use the index to actually speed up the query.

Don't do this, please.

---

Another way. Add a CHAR(32) column to your table and make it your PK. Store in it a MD5 hash of your previous PK columns (i.e. MD5('20001-1-2011-01-07 14:04:40'). Then you can query like: WHERE newPKcolumn = MD5('20001-1-2011-01-07 14:04:40'). This will allow you to do what you want, and MySQL to use the index. The table is no longer normalised, but denormalisation is a tradeoff you need to do sometimes to improve performance or usability. There's nothing wrong with that.

回答3:

You could create a stored procedure that takes '20001-1-2011-01-07 14:04:40' (a string) as an argument, then parse it and make the SELECT statement inside the procedure.

回答4:

No possible to do it like that. A stored procedure to pre-parse is one way to get this done. If you do not have to stick with this table design the I would suggest to change the primary key to a new column which you can set to autoincrement.

If you have to stick to this design then you can still add a new 'mapping' table which would as the name suggests map your combination to a primary key:

CREATE TABLE IF NOT EXISTS `bbb_mapping` (
  `YourPK` int(11) NOT NULL AUTO_INCREMENT,
  `OfficeCode` int(5) NOT NULL,
  `IssuerId` int(11) NOT NULL,
  `BulletinDtm` datetime NOT NULL
  PRIMARY KEY (`YourPK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Using this approach you can join the mapping table with your original table while using YourPK in the querystring.

Cheers