Sins of MySQL Syntax
10/30/2010I know many people like MySQL, but it continues to thumb its nose at interoperability... It has a lot of gotchas.
Some weirdness that I've encountered lately:
Create table
DROP TABLE ... [RESTRICT | CASCADE]
Is accepted by the parser but does nothing .
SELECT DISTINCTROW
is the same thing as using DISTINCT
.
This entire post: "SQL Lexing madness"
CREATE TABLE bar (m INT) SELECT n FROM foo;
That magically creates a table with columns 'm' and 'n' .
Also, don't bother using check()
on CREATE TABLE
, it does nothing.
If the engine you want is not enabled, MySQL will silently create the table using MyISAM.
Standards?
Backslashes in strings and implicit concatenation. Check out the disappearing backslash!
And there seems to be no difference between single and double quoted strings in MySQL...
Fast like /dev/null
Oh, discovered another fun gotcha today, first Postgres:
create table test (one bigint);
CREATE TABLE
insert into test values (9223372036854775807), (9223372036854775808);
ERROR: bigint out of range
MySQL happily stores it:
one
===================
9223372036854775807
9223372036854775807