文章目录
- 1.背景
- 2.问题
- 3.分批插入
- 4.一次最多能插入多少条记录？
- 5.什么是 Prepared Statement？
- 参考文献
1.背景
Golang 后台服务使用 GORM 实现与 MySQL 的交互，在实现一个通过 Excel 导入数据的接口时，使用 Save 方法一次性插入大量记录（>1w）时报了如下错误：
Error 1390 (HY000): Prepared statement contains too many placeholders
2.问题
在 MySQL 官方文档 Server Error Message Reference 可以看到其描述。
该错误属于 MySQL 服务端错误，可惜的是，官方文档并未给出详细的错误原因，只给出了错误码的简短描述。错误描述字面意思是“预处理语句包含太多占位符”，结合业务场景，猜测原因是一次插入太多记录。
3.分批插入
既然不允许一次插入太多记录，那么可以改为分批插入，而不是一次性插入所有数据。
// 待插入的记录。
records := parseRecordsFromExcel()
batch := 1000
for i := 0; i < len(records ); i += batch {
end := i + batch
if end > len(records) {
end = len(record)
}
return Db.Save(records[i:end]).Error
}
正如预期的那样，改为分批插入，解决了问题。
不知道你有没有疑问，我这里分批插入每批记录数是 1000，那可以采用 2000 或者其他数量吗？每批插入记录数的上限是多少呢？
带着这个疑问，请继续往下看。
4.一次最多能插入多少条记录？
MySQL 服务端之所以报 Error 1390 (HY000) 错误，直接原因是一次插入过多的记录，但更深层次的原因是 MySQL SQL 语句的占位符数量有上限，最大值为 16bits 无符号整数的最大值（65535）。
可以在 sql/sql_prepare.cc 中看到相关代码：
static bool init_param_array(THD *thd, Prepared_statement *stmt) {
LEX *lex = stmt->m_lex;
if ((stmt->m_param_count = lex->param_list.elements)) {
if (stmt->m_param_count > static_cast<uint>(UINT_MAX16)) {
/* Error code to be defined in 5.0 */
my_error(ER_PS_MANY_PARAM, MYF(0));
return true;
}
...
}
如果是 INSERT 语句，插入 n 条记录，每条记录有 m 列，则要求 m*n <= 65535。
如果数据量很大，最简单的解决方法，就是进行分批插入。
5.什么是 Prepared Statement？
上面的错误信息中提到了 Prepared statement，那么什么是 Prepared statement？为什么插入语句会涉及到占位符呢？
一条 SQL 在 DB 接收到最终执行完毕返回，大致的过程如下：
- 词法和语义解析。
- 优化 SQL 语句，制定执行计划。
- 执行并返回结果。
如果一条 SQL 经历上面所有的流程处理，一次编译，单次运行，此类普通语句被称作立即语句（Immediate Statement）。
但是，绝大多数情况下，某些 SQL 语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要经过上面的词法语义解析、语句优化，则效率明显很低。
如果事先解析优化好 SQL 语句，一次编译，多次运行，这种 SQL 被称为预处理语句（Prepared Statement）。
在 MySQL 中，Prepared Statements 是一种预编译 SQL 语句的机制，它可以帮助提高 SQL 的性能和安全性。
预编译语句的优势在于：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止 SQL 注入。
# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;
下面看一个例子：利用字符串定义预处理 SQL，根据勾股定理计算直角三角形斜边。
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @b = 4;
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)
参考文献
Chapter 2 Server Error Message Reference
How many bind variables can I use in a SQL query in MySQL 5?
MySQL的SQL预处理(Prepared) - GeaoZhang
MySQL 8.0 Reference Manual :: 13.5 Prepared Statements
