仅为一列插入不同的值

Say, I have a table with five columns col1, col2, col3, col4 and user_id. Now, I have an array of user_id values, say a thousand. I want to insert thousand of records where only distinct column value is user_id. If there is a simplier way rather than make a thousand of ('col1value','col2value','col3value','col4value',someUserId) and concatenate them in single insert into tbl (col1,col2,col3,col4,user_id) values query?

比如，我有一个包含五列col1，col2，col3，col4和user_id的表。现在，我有一个user_id值数组，比如一千。我想插入数千条记录，其中只有不同的列值为user_id。如果有一个更简单的方法而不是制作一千个（'col1value'，'col2value'，'col3value'，'col4value'，someUserId）并将它们连接到单个插入到tbl（col1，col2，col3，col4，user_id）值查询？

Update: I guess it needs some clarification

更新：我想它需要一些澄清

So here's simple example. Let's say I have an events table with fields event and user_id. Some call event occurs for users with id 1, 2, 5, 101, 233, 422 and 1000. So I need to insert 7 records into table so it should look like

所以这是一个简单的例子。假设我有一个包含字段event和user_id的事件表。对于ID为1,2,5,101,233,422和1000的用户，会发生一些调用事件。所以我需要在表中插入7条记录，这样它应该看起来像

+-------+---------+
| event | user_id |
|-------|---------|
|  call |       1 |
|  call |       2 |
|  call |       5 |
|  call |     101 |
|  call |     233 |
|  call |     422 |
|  call |    1000 |
+-------+---------+

I want to do it as efficiently database-wise as possible. So far, I think I have to make such SQL query:

我想尽可能有效地在数据库中做到这一点。到目前为止，我认为我必须进行这样的SQL查询：

insert into events (event,user_id) values ('call',1),('call',2),('call',5),('call',101),('call',233),('call',422),('call',1000);

then perform a single query

然后执行单个查询

But maybe there is some more simple and efficient way? Maybe something with SQL parameters or such?

但也许有一些更简单有效的方法？也许是SQL参数之类的东西？

3 个解决方案

#1

I understand your question that you have a high number of different user_id values that you want to insert, but for each new record you want the same values for col1, col2, col3 and col4.

我理解您的问题是您要插入大量不同的user_id值，但是对于每个新记录，您需要col1，col2，col3和col4的相同值。

The easiest way to achieve this would be to set a DEFAULT value for each of the columns:

实现此目的的最简单方法是为每个列设置DEFAULT值：

ALTER TABLE mytable CHANGE `col1` `col1` INTEGER NOT NULL DEFAULT 1234

This sets the default value for col1 to 1234. I assumed the column to be of data type INTEGER, you need to change it accordingly.

这将col1的默认值设置为1234.我假设该列为数据类型INTEGER，您需要相应地更改它。

If changing the table is not an option for you, then you could still build an insert statement that would insert all records in a single transaction:

如果更改表不是您的选项，那么您仍然可以构建一个插入语句，该语句将在单个事务中插入所有记录：

$user_ids = array(5,6, 7, 8, 9, 10); // these are the user ids you want to insert
$default_vals = array(1, 2, 3, 4); // These are the default values used for col1, col2, col3 and col4

$con = new mysqli('mydomain', 'myuser', 'mypw', 'mydb');

$rows = array();
foreach ($user_ids as $id)
    $rows[] = "(".implode(',', $default_vals).",$id)";
$sql = "INSERT INTO mytbl (col1, col2, col3, col4, id) VALUES " . implode(',', $rows) . ";";

$con->query($sql);

Or, if for academic reason you'd rather write a single INSERT statement to do the whole job, you could write:

或者，如果出于学术原因，你宁愿写一个INSERT语句来完成整个工作，你可以写：

INSERT INTO mytbl (col1, col2, col3, col4, id)
SELECT * FROM 
  (SELECT 1, 2, 3, 4) AS DEFAULT_VALS,
  (SELECT 5
   UNION SELECT 6
   UNION SELECT 7
   UNION SELECT 8
   UNION SELECT 9) AS USER_IDS

However, the last approach really isn't very nice. Neither in terms of readability nor of performance.

但是，最后一种方法确实不是很好。无论是在可读性还是性能方面。

Edit
I made a quick benchmark for you:

Inserting 10k different records like in my PHP sample took 1.5086660385132 seconds on my webserver.
在我的PHP示例中插入10k个不同的记录在我的网络服务器上花了1.5086660385132秒。
The SELECT INTO approach with 10k UNIONs took 3.3481941223145 seconds.
使用10k UNION的SELECT INTO方法耗时3.3481941223145秒。

Easy choice, though.

但是很容易选择。

#2

Use :

使用：

INSERT INTO tbl (col1,col2,col3,col4,user_id)
VALUES 
  (1,2,3,4,1000),
  (2,3,4,5,1000),
  (6,7,8,9,1000),
  (1,2,3,4,1234),
  (4,3,2,1,1235);

#3

Create unique index on user_id field. use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE for ignoring duplicate insertion of user_id.

在user_id字段上创建唯一索引。使用INSERT IGNORE或INSERT ... ON DUPLICATE KEY UPDATE忽略重复插入user_id。

For details, kindly refer MySQL documentation.

有关详细信息，请参阅MySQL文档。

http://dev.mysql.com/doc/refman/5.6/en/insert.html

#1