-
General:
- MySQL has several extensions and deviations from the standard. They
claim to be working toward supporting the standard except when it
impacts "speed and quality of the code" (standards).
Operating the MySQL server in ANSI mode will makes its syntax and
behavior "more conformant to the standard SQL" (SQL mode).
Consider using this mode if you want to write more portable SQL.
-
Chapter 2: Retrieval: Basic SELECTion
-
Literal String
Escape Sequence
- In MySQL you can use the '\' character to escape special characters,
like quote('), rather than quoting them. You can also use this escape
character rather than defining your own when using LIKE on literal
strings (String
Syntax).
-
Chapter 3: Reshaping Results
-
String Concatenation
- MySQL treats || as a logical OR, not concatenation. MySQL uses the
CONCAT function for concatenation (String
Functions). If you start the MySQL server with the --ansi option,
MySQL will use || for concatenation.
-
Trailing White Space in VARCHAR Columns
- In MySQL versions prior to 5.0.3 trailing white space is removed
from strings in VARCHAR columns when the value is stored (manual
version 5.1: Differences from ansi).
-
Literal String Escape Sequence
- See this issue in Chapter 2.
-
- MySQL has a limited number of data types that may be used in a CAST,
Numeric is not one of them (cast).
-
- Use MySQL's temporal functions for temporal arithmetic (temporal
functions).
-
Chapter 4: Aggregating Results
-
CAST
- See this issue in Chapter 3
-
GROUP BY with "hidden"
- Consider the following query:
SELECT companyname, referredby
FROM vendors
GROUP BY referredby;
companyname referredby
---------------------- -------------
Veggies_R_Us (null)
Spring Water Supply EDDRS
"Fruit Eating" Friends FLVCR
Ed's Dressings FRTFR
Don's Dairy VGRUS
This is illegal in SQL. The SELECT list may only contain attributes
that appear in the GROUP BY clause unless they are only used in an
aggregate function. MySQL allows this (Group-by
hidden fields).
Note the odd results above. VGRUS refers both Don's Dairy and Flavorful
Creams; however, since we are grouping by referredby, VGRUS may only
show up once. MySQL arbitrarily decides to use Don's Dairy, thereby
excluding Flavorful Creams from the result.
Of course, MySQL warns you about this in the manual:
"Do not use this feature if the columns you omit from the GROUP
BY part are not unique in the group! You get unpredictable results."
- Chapter 5: Joins
-
- As of version 5.0.13, MySQL does not support FULL OUTER JOINs (Join
Syntax).
-
Chapter 6: Set Queries: UNION, INTERSECT, and EXCEPT
-
- As of version 5.0.13, MySQL does not support INTERSECT or EXCEPT.
UNION, however, does work.
-
Chapter 7: Subqueries
-
- In MySQL if a NULL value is on the left hand side of the "IN"
operator the result is NULL rather than 1 or 0. This can cause problems
with subqueries using IN. For example, consider query 7.19:
SELECT companyname
FROM vendors
WHERE referredby NOT IN
(SELECT vendorid
FROM vendors
WHERE companyname = 'No Such Company');
In the ANSI SQL standard, the company name "Veggies_R_Us" is included in
the result set. However, because the referedby value associated with
"Veggies_R_Us" is NULL, MySQL returns NULL rather than 1
for the NOT IN operation and "Veggies_R_Us" is not included
in the result set (Comparison
Functions and Operators).
-
-
MySQL does not support naming derived tables and their attributes using a single AS statment in the form:
SELECT *
FROM (SELECT att1, att2, att3) AS d(a1, a2, a3)
Instead each attribute must have its own AS statement in the SELECT clause of the subquery like this:
SELECT *
FROM (SELECT att1 AS a1, att2 AS a2, att3 AS a3) AS d
This syntax will give the exact same results as using the single AS
statement in ANSI (Subqueries
in the FROM Clause).
-
EXCEPT
- As of version 5.0.13, MySQL does not support EXCEPT (see this issue
in Chapter 6).
-
Chapter 8: Modifying Data
-
- To insert an entire row of default values in MySQL use the following format:
INSERT INTO table VALUES();
Rather than the ANSI format:
INSERT INTO table DEFAULT VALUES;
These statements have identical results, its merely a matter of MySQL
syntax (INSERT
Syntax).
-
FULL OUTER Joins
- See this issue in Chapter 5
-
Chapter 9: Creating, Deleting, and Altering Tables
-
DEFAULT
- The default value in MySQL must be a constant. Expressions and functions
are not allowed (create table).
This means that you cannot have the following column declaration:
dateadded DATE DEFAULT CURRENT_DATE
CURRENT_DATE is a function call. The one (weird) exception to
this rule is that you can set the DEFAULT value of a TIMESTAMP to
CURRENT_TIMESTAMP. Beware that MySQL has some strange rules about
TIMESTAMP so read the manual before using this type
-
- In MySQL you must include the item in addition to the table referenced
when naming constraints (Foreign
Key Constraints). You cannot issue the command:
CONSTRAINT z FOREIGN KEY(x) REFRENCES y
but rather must use the full syntax:
CONSTRAINT z FOREIGN KEY(x) REFERENCES y(a)
-
- In MySQL you cannot use this syntax (Create
Syntax):
CREATE TABLE x(a1, a2, a3) AS
SELECT att1, att2, att3
FROM tables
Instead you should use this syntax:
CREATE TABLE x
SELECT att1 AS a1, att2 AS a2, att3 AS a3
FROM tables
-
SELECT INTO
- MySQL does not support the SELECT INTO query. Instead they support
INSERT INTO table (fieldID). However, in this method the table must
already exist. If you want to create the table in the query you have
to use a CREATE clause(Select
Into).
-
Domains
- MySQL does not currently support Domains.
-
- MySQL does not support the DROP CONSTRAINT command. Rather you may
use the DROP FOREIGN KEY command, but cannot specify RESTRICT or CASCADE
(ALTER
TABLE Syntax).
-
- MySQL does not support the "GENERATED BY" syntax. You can use the
"AUTO_INCREMENT" attribute instead, if applicable (AUTO_INCREMENT
syntax).
-
Sequences
- MySQL currently does not support sequences.
-
- MySQL does not implement the concept of a "LOCAL" temporary table
(CREATE
TABLE Syntax).
-
Chapter 10: Views
MySQL 4.X does not support views. MySQL 5.X does (views).
-
Updatable Views
- As of MySQL v. 5.1, not all views are updatable in MySQL, even some
that theoretically should be (Restrictions
on Views). Insert 10.11 will not work on MySQL because vrs is not updatable.
-
CAST
- See this issue in Chapter 3
-
Chapter 11: Transactions
-
DEFERRED Constraints
- In MySQL, all contraints are enforced immediately so there are no
deferred constraints (constraints).
-
Chapter 12: Database Privileges
-
REVOKEs without GRANTs
- MySQL generates an error if you try to revoke privileges that were not granted, so DCL 12.9 will cause
an error.
-
ROLEs
- MySQL does not currently support roles.
-
Chapter 13: Introduction to Cursors, Embedded SQL, Stored Procedures, and Triggers
-
CREATE PROCEDURE and FUNCTION Syntax
- MySQL syntax for stored procedures and functions is different than
that presented in the book (Procedure/Function
Create Syntax).
-
CREATE TRIGGER Syntax
- MySQL syntax for triggers is different than that presented in the
book (Trigger
Create Syntax).
Unfortunately, MySQL does not support
- the "ON UPDATE OF <column>" syntax, so we cannot
replicate DDL13.3 correctly in MySQL.