没有错误信息。 不过发现如果将“-2”改成“'-2'”则运行正常: -- 运行成功 SELECT COUNT(*) AS num FROM `see901`.`hf_content_check` WHERE `status` NOT IN (99,0,'-2') AND `siteid`=1 LIMIT 1--运行成功 SELECT COUNT(*) AS num FROM `see901`.`hf_content_check` WHERE `status` NOT IN (99,0) AND `siteid`=1 LIMIT 1 --运行后自动关闭 SELECT COUNT(*) AS num FROM `see901`.`hf_content_check` WHERE `status` NOT IN (99,0,-2) AND `siteid`=1 LIMIT 1
mysql> DESC hf_content_check; +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | checkid | char(15) | NO | MUL | NULL | | | catid | smallint(5) unsigned | NO | | 0 | | | siteid | smallint(5) unsigned | NO | | 0 | | | title | char(80) | NO | | NULL | | | username | char(20) | NO | MUL | NULL | | | inputtime | int(10) unsigned | NO | | 0 | | | status | tinyint(1) unsigned | NO | MUL | 0 | | +-----------+----------------------+------+-----+---------+-------+ 7 rows in set (0.01 sec)
status是unsigned的,怎么还有-2?判断一下再运行
在MYSQL 5.5下测试了一下 SELECT * FROM aa1 WHERE ff NOT IN(-1) ff是unsigned的,运行没有问题
没有错误信息。
不过发现如果将“-2”改成“'-2'”则运行正常:
-- 运行成功
SELECT COUNT(*) AS num FROM `see901`.`hf_content_check`
WHERE `status` NOT IN (99,0,'-2') AND `siteid`=1
LIMIT 1--运行成功
SELECT COUNT(*) AS num FROM `see901`.`hf_content_check`
WHERE `status` NOT IN (99,0) AND `siteid`=1
LIMIT 1
--运行后自动关闭
SELECT COUNT(*) AS num FROM `see901`.`hf_content_check`
WHERE `status` NOT IN (99,0,-2) AND `siteid`=1
LIMIT 1
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| checkid | char(15) | NO | MUL | NULL | |
| catid | smallint(5) unsigned | NO | | 0 | |
| siteid | smallint(5) unsigned | NO | | 0 | |
| title | char(80) | NO | | NULL | |
| username | char(20) | NO | MUL | NULL | |
| inputtime | int(10) unsigned | NO | | 0 | |
| status | tinyint(1) unsigned | NO | MUL | 0 | |
+-----------+----------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
SELECT * FROM aa1 WHERE ff NOT IN(-1)
ff是unsigned的,运行没有问题
现在明白了,应该是字段溢出导致的。
有些惊讶,mysql如果字段类型没对上,居然会直接出错然后自动关闭。
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| checkid | char(15) | NO | MUL | NULL | |
| catid | smallint(5) unsigned | NO | | 0 | |
| siteid | smallint(5) unsigned | NO | | 0 | |
| title | char(80) | NO | | NULL | |
| username | char(20) | NO | MUL | NULL | |
| inputtime | int(10) unsigned | NO | | 0 | |
| status | tinyint(1) unsigned | NO | MUL | 0 | |
+-----------+----------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
SELECT * FROM aa1 WHERE ff NOT IN(-1)
ff是unsigned的,运行没有问题远程只用“IN(-2)”不会报错,但是“IN(99,0,-2)”报错,“IN(99,0)”不报错。
---也是正常不报错
SELECT COUNT( * ) AS num
FROM `2ee`.`v9_content_check`
WHERE `status` NOT IN (-2) AND `siteid` =1
LIMIT 1
运行没有问题