6

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

eggbertx
  • 463
  • 4
  • 14
  • Sorry, it's been quite a while since I used this site, aside from using others' questions and answers, but this time I couldn't find one for my own – eggbertx Sep 22 '13 at 02:39
  • 2
    not sure why anyone would downvote this question. It is clear, succinct and shows an example. – Mitch Wheat Sep 22 '13 at 02:39
  • there should be some ways around for example: sqlite-utils insert dogs.db dogs dogs.json --pk=id https://sqlite-utils.datasette.io/en/stable/cli.html#cli-inserting-data or even https://blog.mclaughlinsoftware.com/2021/05/24/mysql-insert-set/ – Jan Sep 03 '21 at 04:36

4 Answers4

4

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.

Uwe Trotzek
  • 150
  • 9
3

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....

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Ah, alright, considering in most cases, the statements contain variables from outside the string, that might not always line up, ut it's better than nothing I guess – eggbertx Sep 22 '13 at 02:22
  • Line up column names and values? I would never try that. My preference is to have one field per line, with the commas at the start of the line. Same thing for the values. If things go wrong, it makes troubleshooting a whole lot easier. – Dan Bracuk Sep 22 '13 at 02:49
  • @Dan Bracuk : I also mostly do that , BUT when you get past 25 columns (say) you and I both know how easy it is to mix up the position of two columns of the same type! ;) I did it just 2 days ago! – Mitch Wheat Sep 22 '13 at 02:50
  • 25? I get mixed up after 3. But, I digress. Do you really have your commas at the end of the line? – Dan Bracuk Sep 22 '13 at 03:07
  • sometimes, sometime not. Most TSQL doesn't get edited much after it 'settles down', so it not like it matters a great deal. – Mitch Wheat Sep 22 '13 at 03:11
  • So just to make it easier, I'm doing something a bit like the second example in the post above. That way I can just start at the top of the column list and count my way down, and do the same for the values list – eggbertx Sep 22 '13 at 20:24
0

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.

adius
  • 13,685
  • 7
  • 45
  • 46
-1

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.
jemeshsu
  • 1,784
  • 16
  • 14