Mysql Select record where PRIMARY key = x

Question

I have a primary key in my mysql table that consists 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 for selecting an entry for a given primary key value.

I tried.

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

Instead of a long manual method,

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

What is the standard when working with php and compound keys in your table. Note. I do not want to add auto-increment keys to my tables to solve this problem. If this is not possible, I will just pass the three restrictions in my url.

Answer 1

I see two parts of your question. The first part deals with a reference to a compound value. I'm not sure if MySQL supports this, but this will be the standard SQL way:

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

. .

Answer 2

, MySQL, SQL, .

20001-1-2011-01-07 14:04:40 strng PHP MySQL-.

, ( epsecially InnoDB)

INT(5) - , INT(11) ( INT). TINYINT, SMALLINT MEDIUMINT

---

, , / . , .

---

A : WHERE CONCAT(OfficeCode,IssuerId,BulletinDtm) = '20001-1-2011-01-07 14:04:40'

HORRIBLE, MySQL .

, .

---

. CHAR(32) . MD5 PK (, MD5 ('20001-1-2011-01-07 14:04:40'). : WHERE newPKcolumn = MD5('20001-1-2011-01-07 14:04:40'). , , MySQL . , - , , . .

Answer 3

, '20001-1-2011-01-07 14:04:40' (), SELECT .

Answer 4

It is impossible to do so. A stored procedure for preliminary analysis is one way to do this. If you do not need to adhere to this table design, I would suggest changing the primary key to a new column that can be set to auto-increment.

If you need to adhere to this design, you can still add a new mapping table, which, if the name prompts you to match your combination with the 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 the source table when using YourPK in the query.

Greetings