标题: 'INSERT INTO table …' [打印本页] 作者: 拉斯德乌拉 时间: 2015-12-26 07:47:31 标题: 'INSERT INTO table …'
MySQL has a large number of usefull features that do not seem to be known by many professionalgrammers and DBAs. this newsletter gives a brief list of such features.SQL_CALC_FOUND_ROWSonce It is vitalto extract knowledgelimited by sureselection of records at one time and get total selection of records meeting the condition, bear in mind the next sublimesolution:SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE id > 100 LIMIT 10;SELECT FOUND_ROWS();First query return 10 records meeting the condition. Second query returns selection of records that are meant to go back previous query composed without LIMIT.INSERT IGNOREwhile you insert new record right into a table having basicor unique key and wish to circumvent duplicate key error, have a lokon INSERT IGNORE statement. typicallyyou'll be able to solve duplicate key conflicts on PHP as follows:?// discover a row?$row = query('SELECT * FROM table WHERE id=1');?// if there is not any row, insert new one?if (!$row) {?query('INSERT INTO table …');?}But we will be able to do the similar with simply1 MySQL query without PHP:INSERT IGNORE INTO table …ON DUPLICATE KEY UPDATEthere's common task -?if the thing exist update it, othersensiblecreate new one. that is how we will be able to do it on PHP: ?// search for record by surecondition?$row = query('SELECT * FROM `table` WHERE id=1');?// if record exists?if ($row) {?// update it?query('UPDATE `table` SET value=value+1 WHERE id=1');?}?else {?// insert new record?query('INSERT INTO `table` SET value=1, id=1');?}we will be able to do the similar with simply1 MySQL query without PHP assuming table has basicor unique key on ID:INSERT INTO `table` SET value=1, id=1 ON DUPLICATE KEY UPDATE value=value+1ALTER IGNORE TABLE?Assume there's table with the next structure and information:TestTab (f1 int, f2 int, f3 varchar(20))? ? f1?? ? f2???? f3????---------------------------? ?? 1?? ?? 2??? row?1?????? 1????? 2??? row 2??? ?? 1?? ?? 3??? row 3???? ?? 2?? ?? 3??? row 4??? ?? 3?? ?? 4??? row 5?Now it's required to take away duplicate entries in first two columns from the table. we will be able to try this by running the next MySQL query:ALTER IGNORE TABLE `TestTab` ADD UNIQUE INDEX(f1,f2);More articles about MySQL and other databases can also be found at http://www.convert-in.com/articles.htm
Unexplored MySQL
??
Kareem Abdul Jabbar Jerseys
Dwyane Wade Jersey
Anthony Davis Jerseys
Dirk Werner Nowitzki Jerseys