问题描述:

I have a table schema like this:

CREATE TABLE statuses

(

id int,

lang int,

name varchar(30)

);

ALTER TABLE `statuses` ADD PRIMARY KEY (`id`, `lang`) ;

ALTER TABLE `statuses` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT ;

This table holds some data which might be in different languages. Therefore I want every row which corresponds to the same data but in different languages to have the same id but different lang. This way the dual key is unique. I want to use the following query to insert and update values.

INSERT INTO statuses (id, lang, name)

VALUES (null,1,'Third'),(null,2,'Trzeci')

ON DUPLICATE KEY UPDATE name = VALUES(name)

It works fine if I want to update the values, but it breaks when I want to actually insert something because the autoincrement feature puts different values in id column for every newly created row.

Here you can check SQLfiddle: http://sqlfiddle.com/#!2/62fbfd/1

Is it possible to correct my table structure or my sql query to do the job, or should I use multiple queries first to insert first row to indicate new id and after that insert the rest of the rows with this newly created id? I prefer to use just one query so that's why I ask here.

网友答案:

If you change your schema to this:

CREATE TABLE statuses 
(
 id int, 
 lang int, 
 name varchar(30)
);

ALTER TABLE `statuses` ADD UNIQUE INDEX (`id`, `lang`) ;

ALTER TABLE  `statuses` CHANGE  `id`  `id` INT;

Will that do what you want? The inserts worked when I tried them in your sqlfiddle...

You could solve your id issue (if you need auto_increment) by having another table that contains just the auto_increment values you need. So you insert into that table, get and id and then use that id in your inserts. But you cannot use auto_increment and then use the same id on several rows. If that makes any sense...

网友答案:

I solved my problem this way (by using two queries, but one fairly simple and with the function LAST_INSERT_ID()): http://sqlfiddle.com/#!2/b400ae/1

INSERT INTO statuses (id, lang_id, name) VALUES (null,(SELECT lang_id FROM languages LIMIT 1),'');
INSERT INTO statuses (id, lang_id, name) VALUES 
(LAST_INSERT_ID(),1,'Third'),(LAST_INSERT_ID(),2,'Trzeci'),(LAST_INSERT_ID(),3,'Terzo')
ON DUPLICATE KEY UPDATE name = VALUES(name);
相关阅读:
Top