导入到新机器后，MySQL ENUM 列将不匹配引用值

Question

最近我导入了一个新数据库以在我的本地计算机上进行开发，但是它不起作用:ENUM 列仅在发送不带引号的变量时才起作用。这是一个例子:

mysql.local>select count(*) from psh_products where active = 1;
+----------+
| count(*) |
+----------+
|    72782 |
+----------+
1 row in set (0.04 sec)

mysql.local>select count(*) from psh_products where active = '1';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

如果您想了解表结构:

CREATE TABLE `psh_products` (
  `productID` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `catID` int(2) unsigned NOT NULL,
  `main_sku` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `sku` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `shortsku` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `upc` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `quantity` int(2) unsigned NOT NULL,
  `buy_now` decimal(11,2) NOT NULL,
  `seller_cost` decimal(11,2) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `codedir` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `basic_colorID` int(2) unsigned NOT NULL,
  `manu_colorID` int(2) unsigned NOT NULL,
  `brandID` int(2) unsigned NOT NULL,
  `matID` int(2) unsigned NOT NULL,
  `sizeID` int(2) unsigned NOT NULL,
  `size_sID` int(2) unsigned NOT NULL,
  `styleID` int(2) unsigned NOT NULL,
  `featID` int(2) unsigned NOT NULL,
  `occID` int(2) unsigned NOT NULL,
  `widthID` int(2) unsigned NOT NULL,
  `width_sID` int(2) unsigned NOT NULL,
  `genderID` int(2) unsigned NOT NULL,
  `gender_sID` int(2) unsigned NOT NULL,
  `hits` int(2) unsigned NOT NULL,
  `active` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `tags` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tmp_img` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `imgact` enum('again','flip','resize','moderated','badimg','badimgm','badimga') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'moderated',
  `status` enum('new','moderated') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'moderated',
  `quanflag` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `deleted` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`productID`),
  KEY `idx_cid` (`catID`),
  KEY `idx_bid` (`brandID`),
  KEY `idx_act` (`active`),
  KEY `idx_act_hits` (`active`,`hits`),
  KEY `idx_wid` (`widthID`),
  KEY `idx_sid` (`sizeID`),
  KEY `idx_styleid` (`styleID`),
  KEY `idx_sku` (`sku`),
  KEY `idx_msku` (`main_sku`),
  KEY `idx_matid` (`matID`),
  KEY `idx_cid_mstyleid` (`catID`,`featID`),
  KEY `idx_shortsku` (`shortsku`),
  KEY `idx_quant` (`quantity`),
  KEY `idx_quanflag` (`quanflag`),
  KEY `idx_hits` (`hits`),
  KEY `idx_act_qua_cat` (`active`,`quantity`,`catID`),
  KEY `idx_act_qua_cat_sho` (`active`,`quantity`,`catID`,`shortsku`),
  KEY `idx_bcolor_id` (`basic_colorID`),
  KEY `idx_mcolor_id` (`manu_colorID`),
  KEY `idx_cdate` (`cdate`),
  KEY `idx_deleted` (`deleted`),
  KEY `occID` (`occID`),
  KEY `idx_fid` (`featID`),
  KEY `width_sID` (`width_sID`,`genderID`,`gender_sID`)
) ENGINE=InnoDB AUTO_INCREMENT=72790 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

欢迎任何想法!

Answer 1

请注意，当您在第一个查询中针对枚举数据类型的查询中使用数值时，该数值将被视为索引，而不是枚举值之一。

所以，查询

select count(*) from psh_products where active = 1;

真的等同于

select count(*) from psh_products where active = '0';

因为“0”是枚举中的第一项(索引 1)。

由于这种困惑，the documentation明确指出“我们强烈建议您不要使用数字作为枚举值。”