将多个值插入同一列名+覆盖/更新现有条目?

时间:2021-04-23 10:03:15

This is a 2 part question which is pretty related, hence why I combined it into one:

这是一个非常相关的2部分问题,因此我将其合并为一个问题:

Part 1

I have the arrays $country and $redirect each with up to 3 different values (a sum of six between them).

我有数组$ country和$ redirect,每个数组最多有3个不同的值(它们之间的总和为6)。

I however, only have two columns in my table, country and redirect. I would like to use an insert query that would insert $country[0], $country[1], etc into the column country and the same for $redirect[0], $redirect[1], etc with the column redirect.

但是,我的表,国家和重定向只有两列。我想使用插入查询将$ country [0],$ country [1]等插入列国家,并使用列重定向的$ redirect [0],$ redirect [1]等相同。

This might be a stupid question, but would this INSERT query work, simply looping values to columns?

这可能是一个愚蠢的问题,但这个INSERT查询是否有效,只是将值循环到列?

INSERT INTO table_name (country, redirect) values ('$country[0]', '$redirect[0]', '$country[1]', '$redirect[1]', '$country[2]', '$redirect[2]')

If not, how could I get this to work? I could use a for loop, but I'm concerned about the resource usage.

如果没有,我怎么能让它工作?我可以使用for循环,但我担心资源使用情况。


Part 2

How do I overwrite/update a row, where the value of column country already exists.

如何覆盖/更新列国家/地区的值已存在的行。

For example, Great Britain already exists within the column country with http://en-gb in the same row but within the column redirect.

例如,英国已存在于列国家/地区,http:// en-gb位于同一行但位于列重定向内。

I have an INSERT query such as INSERT INTO table_name (country, redirect) VALUES ('Great Britain', 'http://uk'), and I'd like to overwrite/update the row where Great Britain already exists within column country with the new redirect value.

我有一个INSERT查询,例如INSERT INTO table_name(country,redirect)VALUES('Great Britain','http:// uk'),我想覆盖/更新列英国已存在的行使用新的重定向值。

Any answers/comments would be very, very, very much appreciated :)!!

任何答案/评论将非常,非常,非常赞赏:) !!

1 个解决方案

#1


4  

To your first question, do it like this and it will work:

对于你的第一个问题,这样做,它会工作:

INSERT INTO table_name (country, redirect) 
VALUES ('$country[0]', '$redirect[0]'), 
       ('$country[1]', '$redirect[1]'), 
       ('$country[2]', '$redirect[2]')

To your second question: You need a unique index on country and then can use INSERT ON DUPLICATE KEY UPDATE:

对于你的第二个问题:你需要一个国家的唯一索引,然后可以使用INSERT ON DUPLICATE KEY UPDATE:

INSERT INTO table_name (country, redirect) VALUES ('Great Britain', 'http://uk')
  ON DUPLICATE KEY UPDATE redirect='http://uk';

Or with multiple values:

或者有多个值:

INSERT INTO table_name (country, redirect) 
VALUES ('$country[0]', '$redirect[0]'), 
       ('$country[1]', '$redirect[1]'), 
       ('$country[2]', '$redirect[2]')
ON DUPLICATE KEY UPDATE redirect=VALUES(redirect)

#1


4  

To your first question, do it like this and it will work:

对于你的第一个问题,这样做,它会工作:

INSERT INTO table_name (country, redirect) 
VALUES ('$country[0]', '$redirect[0]'), 
       ('$country[1]', '$redirect[1]'), 
       ('$country[2]', '$redirect[2]')

To your second question: You need a unique index on country and then can use INSERT ON DUPLICATE KEY UPDATE:

对于你的第二个问题:你需要一个国家的唯一索引,然后可以使用INSERT ON DUPLICATE KEY UPDATE:

INSERT INTO table_name (country, redirect) VALUES ('Great Britain', 'http://uk')
  ON DUPLICATE KEY UPDATE redirect='http://uk';

Or with multiple values:

或者有多个值:

INSERT INTO table_name (country, redirect) 
VALUES ('$country[0]', '$redirect[0]'), 
       ('$country[1]', '$redirect[1]'), 
       ('$country[2]', '$redirect[2]')
ON DUPLICATE KEY UPDATE redirect=VALUES(redirect)