如果是唯一的，如何进行MySQL插入，但是对于唯一索引，列太长

I have found a great answer for when inserting a new record, ignore if the data already exists.

我在插入新记录时找到了一个很好的答案,如果数据已经存在则忽略。

1) Create a UNIQUE INDEX on the columns.

1)在列上创建UNIQUE INDEX。

2) INSERT IGNORE INTO ...

2)INSIGN IGNORE INTO ...

But my problem is that one of the columns is a VARCHAR(2000)**, and MySQL has a 1000-character limit to indexes.

但我的问题是其中一列是VARCHAR(2000)**,MySQL对索引有1000个字符的限制。

The columns are: id (int), type (varchar 35), data (varchar 2000)

列是:id(int),type(varchar 35),data(varchar 2000)

So is there a way to make sure I'm not adding the same data twice with a single query? Or do I need to do a select first to check for existence and if false, perform the insert?

那么有没有办法确保我没有使用单个查询两次添加相同的数据?或者我是否需要首先选择检查是否存在,如果为false,则执行插入?

Thanks.

** This is not design, I'm just moving data around so no chance of making this column smaller.

**这不是设计,我只是移动数据,所以没有机会使这个列更小。

1 个解决方案

#1

Given the table design you mentioned:

鉴于你提到的桌子设计:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    type varchar(35),
    data varchar(2000),
    primary key (id)
);

Your best chance would be the following:

你最好的机会如下:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD COLUMN data_hash CHAR(40);
ALTER TABLE mydb.mytable_new ADD UNIQUE INDEX (data_hash);

INSERT INTO mydb.mytable_new (id,type,data,data_hash)
  SELECT id,type,data,UPPER(SHA(data)) FROM mydb.mytable;

ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;
DROP TABLE mydb.mytable_old;

Once you add this new column and index, table should now look like this:

添加此新列和索引后,表现在应如下所示:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    type varchar(35),
    data varchar(2000),
    data_hash char(40),
    primary key (id),
    unique key data_hash (data_hash)
);

Simply perform your operations as follows:

只需执行以下操作:

INSERTs

INSERT INTO mydb.mytable (type,data,data_hash)
VALUES ('somtype','newdata',UPPER(SHA('newdata')));

INSERTs should fail on data_hash is you attempt a duplicate key insertion

如果您尝试重复键插入,则INSERT应该在data_hash上失败

SELECTs

SELECT * FROM mydb.mytable
WHERE data_hash = UPPER(SHA('data_I_am_searching_for'));

Give it a Try !!!

试试看 !!!