Question

我正在使用PDO并准备发言,但是,如果把ENUM的田地与一个星体

例:

$db = new PDO( mysql:host=localhost;dbname=**** , *** , *** ); $s = $db->prepare( SELECT id FROM t2 WHERE lang = ? ); $s->execute(array( en )); // Works print_r($s->fetchAll()); $s->execute(array(2)); // Does not work print_r($s->fetchAll());

页: 1 表格中的测试:

DROP TABLE IF EXISTS t2; CREATE TABLE t2 ( id int(10) NOT NULL AUTO_INCREMENT, lang enum( no , en , fr ) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO t2 (id, lang) VALUES (NULL , en );

关于如何做到这一点的想法?

我改写为PDO,我更喜欢不重复所有不变、 en和问询。

Answer 1

<代码>2不是有效的ENUM要素。这简便。

它在MySQL的原始材料中工作的原因是,你在lang = 2时,暴露了该清单的基本储存机制(实质上,它只是一种正常价值,但正常化则由ENUM一栏打着)。

我建议不要这样做。 MySQL出于某种原因隐瞒了执行情况。确信在比较中使用<代码>2只不过是magic number ......

Answer 2

语言斜体为 en,无动于don t use enums,而是使用单独的研究/类型表格:

create table languages
(
  lang_id tinyint unsigned not null auto_increment primary key,
  name varchar(255) unique not null
)
engine=innodb;

create table customers
(
  cust_id int unsigned not null auto_increment primary key,
  lang_id tinyint unsigned not null,
  foreign key (lang_id) references languages(lang_id)
)
engine=innodb;

you也应当认识到,在ENUM定义中增加新的价值,将需要MySQL重建整个表格——在大型表格中不低于最佳程度!

Answer 3

Reasoning:
I agree that you should not have to select anything by enum index or even use database enums at all, but there are always exceptions and special cases.
In my case, it s a legacy system that is of course meant to be replaced by a new system, but that will take at least one year, possibly way longer. Any database changes to the legacy system have a large impact and we try to keep them at a minimum. We do have a small table with users groups, and the group ids are also set as enums. So yes, adding a group does also require adding another enum value to that table s id column.
I do have no knowledge about how this came to be and I don t question it, I just have to deal with it until we can completely replace it.

我想提供一个简单的用户管理,因为现在任何用户变动都必须经过一个漫长的过程,使开发商直接在数据库中进行变革。用户群体按比例指数排列,我希望过滤行政部门的现有群体,使其低于或等于目前挂牌用户群体。

短篇; 为了在一份准备的发言中介绍这项工作,我这样做:

SELECT `groups`.*, `groups`.`id`+0 AS groupId FROM groups WHERE `groups`.`id`+0 <= ?;

Explanation:
By adding +0 to the column, MySQL automatically treats the column as an integer and uses the enum index instead of the value. It s a simple trick that may cause other problems, but I didn t encounter any so far.

友情链接