Roland,
thanks for bringing all of these syntax constructs together.
In fact, there is one construct that MySQL does not implement; the
<contextually typed row value constructor> with a single expression and
no enclosing parentheses.
Example:
CREATE TABLE T(a INT, b INT)
INSERT INTO T(A) VALUES 1, 2, 3, 99;
In MySQL you need to write:
INSERT INTO T(A) VALUES(1),(2),(3),(99);
Thanks,
Roy
Roland Bouman wrote:
Forgot to reply to list.
short story: multiple row insert is standard sql as far as i can see.
On Wed, Dec 17, 2008 at 3:56 AM, Roland Bouman <[email protected]> wrote:
Hi!
On Wed, Dec 17, 2008 at 1:59 AM, Konstantin Osipov <[email protected]> wrote:
* Jay Pipes <[email protected]> [08/12/16 22:30]:
a) Multi-row INSERT is not standard, it's a MySQL extension.
Nope - it is in fact standard SQL. MySQL does not support all possible
variations, but as you can see here, the supported variants are in
fact valid productions of the standard syntax:
<insert statement> ::= INSERT INTO <insertion target> <insert columns
and source>
<insertion target> ::= <table name>
<insert columns and source> ::=
<from subquery>
| <from constructor>
| <from default>
<from subquery> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<query expression>
<from constructor> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<contextually typed table value constructor>
<override clause> ::=
OVERRIDING USER VALUE
| OVERRIDING SYSTEM VALUE
<from default> ::= DEFAULT VALUES
<insert column list> ::= <column name list>
<contextually typed table value constructor> ::= VALUES <contextually
typed row value expression list>
<contextually typed row value expression list> ::=
<contextually typed row value expression>
[ { <comma> <contextually typed row value expression> }... ]
<contextually typed row value expression> ::=
<row value special case>
| <contextually typed row value constructor>
<contextually typed row value constructor> ::=
<common value expression>
| <boolean value expression>
| <contextually typed value specification>
| <left paren> <contextually typed row value specification> <right paren>
| <left paren> <contextually typed row value constructor element> <comma>
<contextually typed row value constructor element list> <right paren>
| ROW <left paren> <contextually typed row value constructor element
list> <right paren>
<contextually typed row value constructor element list> ::=
<contextually typed row value constructor element>
[ { <comma> <contextually typed row value constructor element> }... ]
So the path I'm following is:
<insert statement> ::= INSERT INTO <table name> <from constructor>
<from constructor> ::= (<insert column list>) VALUES <contextually
typed row value expression list>
<contextually typed row value expression list> := <contextually typed
row value constructor>, ...., <contextually typed row value
constructor>,....etc.
<contextually typed row value constructor> ::= (<contextually typed
row value constructor element>), ...., (<contextually typed row value
constructor element>) etc.
(and of course, the variant with the subquery is also a multi-row
insert statement which would produce things like INSERT INTO <table>
SELECT .... FROM ...
b) Have you thrown away sql_mode? If yes, you have only one option
for this behaviour, and it perhaps should be "throw an error".
MySQL will also throw an error in this case with
sql_mode=strict_all_tables.
Konstantin, even with sql_mode := TRADITIONAL MySQL inserts a 0 for
the NOT NULL column when loading NULLs with LOAD DATA INFILE....is
that a bug or a feature?
kind regards,
Roland
--
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
--
Roland Bouman
http://rpbouman.blogspot.com/
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
