有没有办法在INSERT语句中命名列？

When I do SELECT statements in PHP code I always select named columns, like:

当我在PHP代码中执行SELECT语句时,我总是选择命名列,如:

SELECT id, name from users;

rather than using:

而不是使用:

SELECT * from users;

This has the advantage of being more informative and readable, and also avoids problems later if new columns are added to the table.

这具有提供更多信息和可读性的优点,并且如果将新列添加到表中,也可以避免以后出现问题。

What I'm wondering is, is it possible to use the same idea in an INSERT statement? I'm imagining it might be something like this:

我想知道的是,是否可以在INSERT语句中使用相同的想法?我想象它可能是这样的:

INSERT into people values (id=1, name="Fred");

The syntax as I've shown in this example doesn't work, but I wonder if something equivalent is possible? If not, does anyone know why not? Is it a deliberate omission?

我在这个例子中显示的语法不起作用,但我想知道是否有可能的等价物?如果没有,有谁知道为什么不呢?这是故意遗漏吗?

9 个解决方案

#1

That's syntax is possible with MySQL only. Afaik, other RDBMS doesn't allow that. Here's the syntax:

只有MySQL才能实现这种语法。 Afaik,其他RDBMS不允许这样做。这是语法:

INSERT INTO games SET yr = 2012, city = 'London';

I wish PostgreSQL facilitated this kind of insert syntax

我希望PostgreSQL能够促进这种插入语法

Standard ANSI syntax, however, would be

但是,标准的ANSI语法是

INSERT into people (yr, city) values (2012, 'London');

插入人(年,城)价值观(2012年,'伦敦');

#2

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

INSERT INTO people (id, name)
VALUES (1, 'Fred');

#3

what you want is

你想要的是什么

INSERT INTO PEOPLE ( id, name ) VALUES ( 1, 'FRED' )

#4

INSERT into people(id,name) values (1, 'Fred');

#5

INSERT INTO table (column_1, column_2, column_3, ...)
VALUES (value_1, value_2, value_3, ...)

#6

Use INSERT INTO <table> (col1, col2) VALUES (1,2).

使用INSERT INTO

(col1,col2)VALUES(1,2)。

#7

Try it this way: INSERT INTO people (id, name) VALUES (1, "Fred");

试试这种方式:INSERT INTO people(id,name)VALUES(1,“Fred”);

#8

the syntax for this case is

这种情况的语法是

INSERT into people (id, name) values (1, 'Fred');

插入人(id,name)值(1,'Fred');

at least it works in the dbms i use (PostgreSQL)

至少它在我使用的dbms中工作(PostgreSQL)

#9

Using standard syntax, you can insert multiple rows at once:

使用标准语法,您可以一次插入多行:

INSERT INTO Table (column1,...,columnN) VALUES
(r1v1, ..., r1vN),
(r2v1, ..., r2vN),
(r3v1, ..., r3vN).

This is MUCH faster way of importing data, than one-row-a-time (especially in MySQL)! I don't think you can do the same using

这是一种更快的导入数据的方式,而不是一行一次(特别是在MySQL中)!我不认为你可以这样做

INSERT INTO Table SET column1 = r1v1, ..., columnN = r1vN

syntax.

(From personal experience, inserting 300000 rows one row a time took several minutes. When I changed the script to use few insert statements only, each inserting several thousands of rows, it took only few seconds to import it all, MySQL 5.0).

(根据个人经验,每次插入300000行一次需要几分钟。当我将脚本更改为仅使用几个插入语句时,每个插入数千行,只需几秒钟即可全部导入,MySQL 5.0)。

#1