Hi Roland, all
On 17/12/2008, at 1:27 PM, Roland Bouman wrote:
short story: multiple row insert is standard sql as far as i can see.
Yep - multi-row insert is standard, it's just not a very widely known
thing so people think it's MySQL-specific because other servers don't
implement it.
Cheers,
Arjen.
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/
--
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
--
Arjen Lentz, Director @ Open Query (http://openquery.com.au)
Training and Expertise for MySQL and related tools
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
