我有一个包含3行的MySQL (5.0)表,它们被认为是一个组合的唯一索引:
CREATE TABLE `test`.`table_a` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`field2` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`field3` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`Id`),
INDEX `IdxUnqiue` (`field1`(5),`field2`(5),`field3`(5))
) ENGINE=MyISAM;此表应填充REPLACE INTO查询:
REPLACE INTO
table_a
(
Field1,
Field2,
Field3
)
VALUES
(
"Test1",
"Test2",
"Test3"
)我希望看到的行为是,此查询始终覆盖先前插入的行,因为IdxUnique is...ahm被触发。但不幸的是,仍然有主索引似乎在起作用,并且总是插入一个新行。
我得到的是:
Query was executed 3 times:
+---Id---+---Field1---+---Field2---+---Field3---+
| 1 | Test1 | Test2 | Test2 |
| 2 | Test1 | Test2 | Test2 |
| 3 | Test1 | Test2 | Test2 |
+--------+------------+------------+------------+我想要的:
Query was executed 3 times:
+---Id---+---Field1---+---Field2---+---Field3---+
| 3 | Test1 | Test2 | Test2 |
+--------+------------+------------+------------+那么,我是否可以告诉REPLACE INTO只使用某个索引,或者考虑一个“更重要”的索引呢?
发布于 2010-04-12 18:16:07
使用INDEX关键字创建的不是unique index,而是一个普通的非惟一索引。这就是为什么允许您使用非distinct (field1、field2、field3)插入行的原因。
因此,您需要做的就是在CREATE TABLE构造中将索引设置为UNIQUE,如下面的示例所示:
CREATE TABLE `table_a` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`field2` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`field3` varchar(5) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`Id`),
UNIQUE `IdxUnqiue` (`field1`(5),`field2`(5),`field3`(5))
) ENGINE=MyISAM;插入测试数据:
REPLACE INTO table_a (Field1, Field2, Field3) VALUES ("Test1", "Test2", "Test3");
REPLACE INTO table_a (Field1, Field2, Field3) VALUES ("Test1", "Test2", "Test3");
REPLACE INTO table_a (Field1, Field2, Field3) VALUES ("Test1", "Test2", "Test3");结果:
SELECT * FROM table_a;
+----+--------+--------+--------+
| Id | field1 | field2 | field3 |
+----+--------+--------+--------+
| 3 | Test1 | Test2 | Test3 |
+----+--------+--------+--------+
1 row in set (0.01 sec)https://stackoverflow.com/questions/2621198
复制相似问题