dongli1920
2012-04-05 20:31
浏览 1.1k
已采纳

MySQL选择IS NOT NULL的多个字段不起作用

I have a query that transfers data from multiple slave tables into a single master table. Here's an example of one of my queries:

$insert = mysql_query("
INSERT IGNORE INTO master (title, description, keywords, url, uniqueid, city, state, zip, datetime, expiretime)
SELECT title, description, keywords, url, uniqueid, city, state, zip, datetime, expiretime
FROM slave1 WHERE zip IS NOT NULL AND keywords IS NOT NULL AND city IS NOT NULL") or die(mysql_error());

After running this query, rows of data are being inserted into master with either some or all of the fields NULL where I specified them to be NOT NULL (the fields being zip, keywords, and city)

It seems like mysql is completely ignoring the IS NOT NULL parameter. Does anyone know a solution?

图片转代码服务由CSDN问答提供 功能建议

我有一个查询将数据从多个从表传输到一个主表中。 以下是我的一个查询的示例:

  $ insert = mysql_query(“
INSERT IGNORE INTO master(标题,描述,关键字,url,uniqueid,city,state,zip)  ,datetime,expiretime)
SELECT标题,描述,关键字,url,uniqueid,city,state,zip,datetime,expiretime 
FROM slave1 WHERE zip IS NOT NULL且关键字IS NOT NULL且city IS NOT NULL“)或die(mysql_error)  ()); 
   
 
 

运行此查询后,数据行将被插入 master ,其中部分或全部 字段 NULL 我指定它们是 NOT NULL (字段是 zip keywords ,和 city

似乎mysql完全忽略 IS NOT NULL 参数。 有没有人知道解决方案?

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doujiaozhan2413 2012-04-05 20:35
    已采纳

    Try replace zip IS NOT NULL to TRIM(zip) <> ""

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douren9077 2012-04-05 20:39

    I suspect uniqueid is the primary key. Probably, your master table already contains rows with the same keys, which have empty values of city, etc. In that case you should use REPLACE instead of INSERT IGNORE.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题