时间戳

首页 > 解决方案 > mysql 多行 INSERT 语法，其中一个占位符实际上是一个常量

mysql - mysql 多行 INSERT 语法，其中一个占位符实际上是一个常量

问题描述

我使用mysql 多行 INSERT 语法将大约 3000 条记录插入到 mysql 表中。为了一个简短的例子，这个表只有两列。实际的表当然有更多的列，其中有一个有意义的索引......

mysql> INSERT INTO table (A, B) VALUES 
("constant", 1), 
("constant", 2),
("constant", 3);

您可能会注意到COLUMN A始终是相同的值。

这让我想到了一个实际的问题：有没有办法将COLUMN A定义为 sql insert 语句中的常量，如以下伪代码中所述：

mysql> INSERT INTO table (A="constant", B) VALUES 
(1), 
(2),
(3);

准备好的陈述...

现在这更具体一点。一些实际的 perl 代码：

#!/usr/bin/perl -W

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect( 'lalalala' );
my $sth = $dbh->prepare( q|INSERT INTO table (A, B) VALUES ("constant",?)| );

foreach my $increment (1 .. 100000){

  $sth->execute( $increment );
}

...

认为这就是我要找的吗？不，不幸的是没有。如前所述，我正在通过 TCP/IP 与 mysql 交谈。上面的代码实际上做的是执行这个

INSERT INTO table (A, B) VALUES ("constant", 1);
INSERT INTO table (A, B) VALUES ("constant", 2);
etc ...

它们中的每一个都在一个 sql 查询中。现在更不用说查询时间了，让我们假设 5ms 的往返时间作为网络开销。在上面的例子中，仅此五秒。现在......我已经编写了一个函数来解决这个混乱并创建一个紧凑的查询，就像你在这个问题的顶部看到的那样。现在想象一个连接字符串作为一个包含一百万个插入的查询。那么它的工作原理。像一个魅力。但是，通过省略 VALUE A 来节省大约 50% 的流量，因为它实际上是一个永远不会改变的常数值会更好。

后经

如果你想知道我为什么问这个以及这一切是关于什么的？它归结为一件事：将一个正在发送的字符串（在我的情况下是通过网络）连接到 mysql，然后当一半的信息是多余的时，它会被拆分以对其进行处理……恕我直言，效率不高。如果有另一种方式 - 很好。如果不是，我会像以前那样做……带着一丝“我觉得不对劲”的感觉

标签： mysqlbulkinsert

解决方案

mysql> INSERT INTO table (A="constant", B) VALUES

No, there is no such syntax in MySQL. Here's the syntax reference page for INSERT in MySQL: https://dev.mysql.com/doc/refman/8.0/en/insert.html

If you want to use multi-row INSERT, you must give an expression for each column in the tuple for every row. The expression can be the same constant on each row, as in your original example:

mysql> INSERT INTO table (A, B) VALUES 
  ("constant", 1), 
  ("constant", 2),
  ("constant", 3);

Or it can be a session variable (as @wchiquito commented above).

mysql> SET @c = 'constant';
mysql> INSERT INTO table (A, B) VALUES 
  (@c, 1), 
  (@c, 2),
  (@c, 3);

Or you can load the values into a temp table and then copy them to the final table (commented by Mauricio Javier Biott above):

mysql> CREATE TEMPORARY TABLE temptable (B INT);
mysql> INSERT INTO temptable (A, B) VALUES 
  (1), 
  (2),
  (3);
mysql> INSERT INTO table (A, B) 
  SELECT 'constant', B FROM temptable;

Here's another solution. Assuming data.txt contains only the fields you need to have a distinct value per row, you could use LOAD DATA LOCAL INFILE, and set the invariant column in a final SET clause.

mysql> LOAD DATA LOCAL INFILE 'data.txt' 
  INTO TABLE mytable (B) 
  SET A = 'constant';

You made another statement in your question:

...assume 5ms roundtrip time as network overhead.

I think that's an improbable assumption. I just measured the network latency of my app in my datacenter, and it's 36 to 50 times smaller than what you are suggesting.

64 bytes from (10.4.12.100): icmp_seq=1 ttl=64 time=0.112 ms
64 bytes from (10.4.12.100): icmp_seq=2 ttl=64 time=0.138 ms
64 bytes from (10.4.12.100): icmp_seq=3 ttl=64 time=0.103 ms
64 bytes from (10.4.12.100): icmp_seq=4 ttl=64 time=0.108 ms
...

That's about a tenth of one millisecond per round-trip, not 5ms.

The network latency of executing a prepared statement 3000 times would be at most 414 milliseconds, not 3000 * 5ms = 15 seconds. If the network latency makes your task have unacceptable overhead, you need to improve your network.

Finally, you might like to see the benchmarks I did for my presentation Load Data Fast! I compare single-row INSERT vs. multi-row INSERT vs. LOAD DATA.

推荐阅读