如果没有唯一键，mysql插入如果不存在 [英] mysql insert if not exist without unique key

问题描述

如MySQL:如果表中不存在则插入记录 和 https://stackoverflow.com/a/5289299/2037323 我正在尝试运行以下查询php 和 $result = mysql_query($query, $con); 返回 true 但新记录不会添加到 teamshalf 表.这里有什么问题?http://pastebin.com/xCqWpKcb 是我用于两个表的数据.这里要提到的一件事是我不能在这里使用唯一键.所以请告诉是否有非唯一的解决方案.

As discussed in MySQL: Insert record if not exists in table and https://stackoverflow.com/a/5289299/2037323 i'm trying to run following query from php and $result = mysql_query($query, $con); returns true but new records are not adding to teamshalf table. Whats wrong here? http://pastebin.com/xCqWpKcb is data that i used for both tables. One thing to mention here is that i can not use unique key here. So please tell if there is solution for non unique.

INSERT teamshalf
(`yearID` , `lgID` , `teamID` , `Half` , 
`divID` , `DivWin` , `Rank` , `G` , `W` , `L`)
 SELECT DISTINCT t1.`yearID` , t1.`lgID` , t1.`teamID` ,
 t1.`Half` , t1.`divID` , t1.`DivWin` , t1.`Rank` , t1.`G` , t1.`W` , t1.`L` 
 FROM `teamshalf_TEMP` t1 LEFT JOIN `teamshalf` t2 ON t1.`yearID` = t2.`yearID`
 AND t1.`lgID` = t2.`lgID` AND t1.`teamID` = t2.`teamID`
 AND t1.`Half` = t2.`Half` AND t1.`divID` = t2.`divID`
 AND t1.`DivWin` = t2.`DivWin` AND t1.`Rank` = t2.`Rank`
 AND t1.`G` = t2.`G` AND t1.`W` = t2.`W` AND t1.`L` = t2.`L`
 WHERE t1.`yearID` IS NULL AND t1.`lgID` IS NULL AND t1.`teamID` IS NULL
 AND t1.`Half` IS NULL AND t1.`divID` IS NULL AND t1.`DivWin` IS NULL
 AND t1.`Rank` IS NULL AND t1.`G` IS NULL AND t1.`W` IS NULL AND t1.`L` IS NULL

解决方案正如@Arjan 所提到的，我使用的是 t1.col_name is null 而不是 t2.col_name is null.所以通过修改我得到了想要的结果.

SOLUTION As mentioned by @Arjan i was using t1.col_name is null instead of t2.col_name is null. So by modifying that i got desired result.

推荐答案

查询成功(否则mysql_query()不会返回true)但不插入任何行.唯一的解释是 SELECT 部分导致一个空集.这是因为 LEFT JOIN 返回存在于 t1 但不在 t2 中的行，但选择是在 NULL t1 中的行.

The query is successful (otherwise mysql_query() would not return true) yet it does not insert any rows. The only explanation for that is that the SELECT part results in an empty set. That is because the LEFT JOIN returns rows that exist in t1 but not in t2, but the select is done on NULL rows in t1.

这应该有效:

INSERT teamshalf
(`yearID` , `lgID` , `teamID` , `Half` , 
`divID` , `DivWin` , `Rank` , `G` , `W` , `L`)
 SELECT DISTINCT t1.`yearID` , t1.`lgID` , t1.`teamID` ,
 t1.`Half` , t1.`divID` , t1.`DivWin` , t1.`Rank` , t1.`G` , t1.`W` , t1.`L` 
 FROM `teamshalf_TEMP` t1 LEFT JOIN `teamshalf` t2 ON t1.`yearID` = t2.`yearID`
 AND t1.`lgID` = t2.`lgID` AND t1.`teamID` = t2.`teamID`
 AND t1.`Half` = t2.`Half` AND t1.`divID` = t2.`divID`
 AND t1.`DivWin` = t2.`DivWin` AND t1.`Rank` = t2.`Rank`
 AND t1.`G` = t2.`G` AND t1.`W` = t2.`W` AND t1.`L` = t2.`L`
 WHERE t2.`yearID` IS NULL

另请注意，mysql_query() 已弃用.并且您应该将必须包含数据的列声明为 NOT NULL，这样 NULL 将不被接受.而且您的查询会很慢，因为您没有使用索引.

Also note that mysql_query() is deprecated. And you should declare columns that must contain data as NOT NULL, so NULL will not be accepted. And your query will be slow, because you're not using indexes.

这篇关于如果没有唯一键，mysql插入如果不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！