Re: how to get the name of primary key ?
Hi, Use *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()* It will help you. Regards, Thiyaghu CK, MySQL DBA www.mafiree.com 2010/2/1 曹凯 tx...@hotmail.com Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
RE: how to get the name of primary key ?
Hi Thiyaghu, I have already got the last_insert_id. now I wanna know if there are any variables or constants to instead of game_log's primary key cos I don't know its name. Date: Mon, 1 Feb 2010 14:24:59 +0530 Subject: Re: how to get the name of primary key ? From: theyaho...@gmail.com To: tx...@hotmail.com CC: mysql@lists.mysql.com Hi, Use *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()* It will help you. Regards, Thiyaghu CK, MySQL DBA www.mafiree.com 2010/2/1 曹凯 tx...@hotmail.com Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1 _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
Re: how to get the name of primary key ?
On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY'; +-+ | COLUMN_NAME | +-+ | GameLogID | +-+ 1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper
RE: how to get the name of primary key ?
hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday
Re: how to get the name of primary key ?
u can also get information of table using use use urdbname mysqlshow create table game_log \G 2010/2/1 曹凯 tx...@hotmail.com hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
