All MySQL-specific information given here is based on version 4.1.13. We welcome any updates, correction, and clarifications. Send them to Jeff_Donahoo at baylor.edu (email address munged to avoid harvesting).

• MySQL Website
• Reference Manual
• Example Database Creation
  • Scripts
    • Restaurant Database Creation Script restaurant-mysql.sql
    • Employee Database Creation Script employees-mysql.sql
  • Instructions
    1. Install MySQL and start database server(installing).
    2. Start MySQL client(connecting).
    3. Execute SQL script at MySQL client prompt: "source <sql file>".
• Resource Sites Below are some sites on MySQL that you may find useful. Please note that I have not verified the accuracy or timeliness of the information contained in these sites.
  • MySQL Gotchas
• MySQL Differences:
  • 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.

    CAST

    MySQL has a limited number of data types that may be used in a CAST, Numeric is not one of them (cast).

    Temporal Operations

    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

    FULL OUTER JOIN

    As of version 5.0.13, MySQL does not support FULL OUTER JOINs (Join Syntax).

  • Chapter 6: Set Queries: UNION, INTERSECT, and EXCEPT

    INTERSECT and EXCEPT

    As of version 5.0.13, MySQL does not support INTERSECT or EXCEPT. UNION, however, does work.

  • Chapter 7: Subqueries

    NULL Comparison

    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).

    Derived Table Names

    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

    Inserting DEFAULT rows

    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

    Naming Constraints

    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)

    CREATE TABLE AS

    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.

    DROP CONSTRAINT

    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).

    GENERATED BY

    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.

    Temporary Tables

    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.