Hi Roy! On Wed, Dec 17, 2008 at 9:04 AM, Roy Lyseng <[email protected]> wrote: > thanks for bringing all of these syntax constructs together.
np, glad to ;) > 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. True. I wrote "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" So I meant, the INSERT syntax supported by MySQL can be derived from the standard grammar so it must be standard, however, there exists some INSERT syntax that can be derived from the standard grammar that MySQL does not support. If I recall my routes through the rules of yesterday night correctly, this is standard SQL too: INSERT INTO <table> VALUES ROW(.., .., ..), ROW(..,..,..),....,ROW(..,..,..) Interesting thing is that MySQL does support the ROW keyword for a row constructor, but just not in this context. (That said, I could do without - the SQL standard grammar sometimes allows quite a few variations with the same semantics) > > 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/ >>> >> >> >> > -- 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
