导入到新计算机后，MySQL ENUM列将不匹配引用值 [英] MySQL ENUM column won't match quoted values after import to new machine

问题描述

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

Recently I've imported a new database to develop on my local machine, however it doesn't work: the ENUM column only works when the variable is sent without quotation marks. Here's an example:

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

欢迎任何想法！

推荐答案

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

Note that when you use a numerical value in a query against an enum data type as in your first query, the numerical value is treated as an index, not as one of the enumerated values.

因此，查询

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

等同于

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

因为"0"是枚举中的第一项(索引1).

since '0' is the first item (index 1) in the enumeration.

由于这种混淆，文档明确声明我们强烈建议您不要将数字用作枚举值."

Because of this confusion, the documentation explicilty states "we strongly recommend that you do not use numbers as enumeration values."

这篇关于导入到新计算机后，MySQL ENUM列将不匹配引用值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！