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