使 SQL INSERT 语句更易读

huangapple go评论106阅读模式
英文:

Making SQL INSERT statement easier to read

问题

我正在使用Go编写一个程序,其中大量使用了MySQL。为了提高可读性,是否可以在INSERT语句中的每个列名后面包含列的值?像这样:

INSERT INTO `table` (`column1` = 'value1', `column2` = 'value2'...);

而不是

INSERT INTO `table` (`column`, `column2`,...) VALUES('value1', 'value2'...);

这样更容易看出哪个值与哪个列相关联,考虑到SQL字符串通常会变得相当长。

英文:

I'm working on a program in Go, that makes heavy usage of MySQL. For sake of readability, is it possible to include the value of a column after each column name in an INSERT statement? Like:

INSERT INTO `table` (`column1` = 'value1', `column2` = 'value2'...);

instead of

INSERT INTO `table` (`column`, `column2`,...) VALUES('value1', 'value2'...);

so that it's easier to see which value is associated with which column, considering the SQL strings can often get fairly long

答案1

得分: 4

虽然这个问题有点旧,但我会把它放在这里供未来的研究者参考。

我建议使用SET语法,而不是丑陋的VALUES列表语法。

INSERT INTO table
SET
column1 = 'value1',
column2 = 'value2';

在我看来,这是MySQL中最清晰的方式。

英文:

Although this question is a bit older I will put that here for future researchers.

I'd suggest to use the SET syntax instead of the ugly VALUES list syntax.

INSERT INTO table
SET
column1 = 'value1',
column2 = 'value2';

IMHO this is the cleanest way in MySQL.

答案2

得分: 3

不,你不能使用你提出的语法(尽管这样做会很好)。

一种方法是将列名和值对齐:

INSERT INTO `table` 
(`column`, `column2`,...) 
VALUES
('value1', 'value2',...);

根据你的评论“语句包含来自字符串外部的变量”的更新:如果你对SQL语句进行参数化处理,那么将列名与变量匹配起来就很容易检查,只需将参数命名为相应的列名:@ColumnName

这实际上是我在我的TSQL脚本中的做法:

INSERT INTO `table` 
(
    `column`, 
    `column2`,
    ...
) 
VALUES
(
    'value1', 
    'value2',
    ...
);

(在行的开头放置逗号也很常见)

但是说实话,一旦你有足够多的列,很容易混淆列的位置。如果它们具有相同的类型(并且值的范围相似),你可能不会立即注意到这一点...

英文:

No, you cannot use your proposed syntax (though it would be nice).

One way is to line up column names and values:

INSERT INTO `table` 
(`column`, `column2`,...) 
VALUES
('value1', 'value2'...);

Update in response to your comment "the statements contain variables from outside the string": if you parameterise your SQL statements then matching up column names to variables is easy to check if the parameters are named for their respective columns: @ColumnName.

This is actually how I do it in my TSQL scripts:

INSERT INTO `table` 
(
    `column`, 
    `column2`,
    ...
) 
VALUES
(
    'value1', 
    'value2',
    ...
);

(It's also common to put the commas at the start of the lines)

but to be honest, once you get enough columns it is easy to mix up the position of columns. And if they have the same type (and similar range of values) you might not notice straight away....

答案3

得分: 0

参考:
SQLite不支持MySQL的set语法,如@uwe-trotzek所述,但可以通过insert后跟update来模拟:

insert into table (column1) values ('value1');
update table set
  column2 = 'value2',
  column3 = 'value3'
where
  column1 = 'value1';

但由于增加了复杂性,这只对插入具有多个列的条目有意义。

英文:

For reference:
SQLite does not support MySQL's set syntax as described by @uwe-trotzek, but it can be emulated with an insert followed by an update:

insert into table (column1) values ('value1');
update table set
  column2 = 'value2',
  column3 = 'value3'
where
  column1 = 'value1';

But due to the added complexity this makes only sense for inserting entries with many columns.

答案4

得分: -1

当你使用Golang的database/sql包时,连接到数据库的工作是由你选择的SQL驱动程序完成的,例如http://github.com/ziutek/mymysql或https://github.com/Go-SQL-Driver/MySQL/。

当你在Query()或Exec()等函数中使用SQL语句时,几乎所有的驱动程序都会将你的SQL语句原样传递给数据库引擎。MySQL实现了标准的SQL INSERT语法,不支持你提出的语法。因此,你不能在现有的Golang MySQL驱动程序和MySQL数据库中使用你提出的语法。

因此,如果你想使用你的语法,你的解决方案是:

  • 找到一个支持你提出的非标准语法的SQL数据库,但据我所知,目前没有这样的数据库。
  • 或者编写自己的Golang MySQL驱动程序,在将其发送到MySQL数据库之前解析和转换你提出的语法为标准的INSERT语法。
  • 或者编写一个database/sql扩展(类似于https://github.com/jmoiron/sqlx),支持你提出的语法。
英文:

When you use Golang database/sql package, the work connecting to the database is carry out by your chosen SQL driver such as http://github.com/ziutek/mymysql or https://github.com/Go-SQL-Driver/MySQL/.

When you use an SQL statement in function such as Query() or Exec(), almost all driver will pass your SQL statement as it is to the database engine. MySQL implements the standard SQL INSERT syntax, and does not support your proposed syntax. Thus you can not use your proposed syntax with existing Golang MySQL drivers and MySQL database.

Thus your solution if you want to use your syntax is to:

  • find an SQL database that supports your proposed non-standard syntax, and there is none that I'm aware.
  • or write your own Golang MySQL driver to parse and convert your proposed syntax to the standard INSERT syntax before sending it to MySQL database.
  • or write a database/sql extension (similar to https://github.com/jmoiron/sqlx) that supports your proposed syntax.

huangapple
  • 本文由 发表于 2013年9月22日 10:18:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/18939631.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定