MySQL:单列多次插入 [英] MySQL: Multiple Inserts for a single column

问题描述

当我只为单个列插入数据时，我正在寻找一种进行多行插入的方法.

I'm looking for a way to do multiple row inserts when I'm only inserting data for a single column.

这是示例表:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | tinyint(4)  | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(40) | NO   | UNI | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+

我希望能够在每一行的名称列中插入类似('admin'，'author'，'mod'，'user'，'guest')的内容.

I want to be able to insert something like ('admin', 'author', 'mod', 'user', 'guest') into the name column for each row.

MySQL文档显示多个插入应采用以下格式:

The MySQL documentation shows that multiple inserts should be in the format:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

但是我的声明最终看起来像这样:

However my statement ends up looking like this:

INSERT INTO User_Role(name) VALUES ('admin','author','mod','user','guest');

我得到以下信息:
错误1136(21S01):列计数与第1行的值计数不匹配

And I get the following:
ERROR 1136 (21S01): Column count doesn't match value count at row 1

表示它认为我正在尝试单行插入.

Meaning that it thinks I'm trying to do a single row insert.

我不确定我是否只是在这里遗漏了一些简单的东西，但是在此用例的MySQL文档中，我看不到任何特别的东西.

I'm not sure if I'm just missing something simple here, but I don't see anything in particular in the MySQL docs for this use case.

推荐答案

您的语法有些偏离.在您要插入的每个数据集合"(在这种情况下，指的是单个值)的周围加上括号.

your syntax is a bit off. put parentheses around each data "set" (meaning a single value in this case) that you are trying to insert.

INSERT INTO User_Roll(name) VALUES ('admin'), ('author'), ('mod'), ('user'), ('guest');

这篇关于MySQL:单列多次插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！