mysql - 如何使用子查询将数据从 View 插入表中？

Question

我在将数据插入表“事件”时遇到小问题

 +-------+----------+------+-----+---------+----------------+
 | Field | Type     | Null | Key | Default | Extra          |
 +-------+----------+------+-----+---------+----------------+
 | id    | int(11)  | NO   | PRI | NULL    | auto_increment |
 | name  | longtext | YES  |     | NULL    |                |
 +-------+----------+------+-----+---------+----------------+

从我看来old_data:

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| event | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

我想将 View old_data 中的“event”字段中的数据插入到表“events”中的“name”字段中，为此我尝试使用命令:

 insert into 'events' ('name') select 'event' from 'old_data'
 insert into 'events' ('name') select 'event' from 'old_data' group by 'event';

等等等等。每次都是语法错误。为什么？!

Answer 1

您在标识符周围使用撇号分隔符是不正确的(它们用于分隔字符串)。尝试:

INSERT INTO events (name) SELECT event FROM old_data;

如果您想在标识符周围使用分隔符，则应使用反引号 (`):

INSERT INTO `events` (`name`) SELECT `event` FROM `old_data`;

来自MySQL manual :

The identifier quote character is the backtick (“`”):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:

mysql> CREATE TABLE "test" (col INT);ERROR 1064: You have an error in your SQL syntax...mysql> SET sql_mode='ANSI_QUOTES';mysql> CREATE TABLE "test" (col INT);Query OK, 0 rows affected (0.00 sec)

The ANSI_QUOTES mode causes the server to interpret double-quoted strings as identifiers. Consequently, when this mode is enabled, string literals must be enclosed within single quotation marks. They cannot be enclosed within double quotation marks. The server SQL mode is controlled as described in Section 5.1.6, “Server SQL Modes”.

Identifier quote characters can be included within an identifier if you quote the identifier. If the character to be included within the identifier is the same as that used to quote the identifier itself, then you need to double the character. The following statement creates a table named a`b that contains a column named c"d:

mysql> CREATE TABLE `a``b` (`c"d` INT);

In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:

mysql> SELECT 1 AS `one`, 2 AS 'two';+-----+-----+| one | two |+-----+-----+|   1 |   2 |+-----+-----+

Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal.