1.6 MySQL Standards Compliance

 --transaction-isolation=SERIALIZABLE --sql-mode=ANSI

 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 SET GLOBAL sql_mode = 'ANSI';

 mysql> SET GLOBAL sql_mode='ANSI';
 mysql> SELECT @@GLOBAL.sql_mode;
         -> 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'

1.6.1 MySQL Extensions to Standard SQL

MySQL Server supports some extensions that are likely not to be found in other SQL DBMSs. Be warned that if you use them, your code is not portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the following form:

 /*! MYSQL-SPECIFIC CODE */

In this case, MySQL Server parses and executes the code within the comment as it would any other SQL statement, but other SQL servers ignore the extensions. For example, MySQL Server recognizes the 'STRAIGHT_JOIN' keyword in the following statement, but other servers do not:

 SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If you add a version number after the '!' character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The 'KEY_BLOCK_SIZE' clause in the following comment is executed only by servers from MySQL 5.1.10 or higher:

 CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

The following descriptions list MySQL extensions, organized by category.

• Organization of data on disk

  MySQL Server maps each database to a directory under the MySQL data directory, and maps tables within a database to file names in the database directory. This has a few implications:

  * 
    Database and table names are case-sensitive in MySQL Server on
    operating systems that have case-sensitive file names (such as
    most Unix systems).  See *note identifier-case-sensitivity::.
  
  * You can use standard system commands to back up, rename, move,
    delete, and copy tables that are managed by the 'MyISAM'
    storage engine.  For example, it is possible to rename a
    'MyISAM' table by renaming the '.MYD', '.MYI', and '.frm'
    files to which the table corresponds.  (Nevertheless, it is
    preferable to use *note 'RENAME TABLE': rename-table. or
    'ALTER TABLE ... RENAME' and let the server rename the files.)

• General language syntax

  * By default, strings can be enclosed by '"' as well as '''.  If
    the 'ANSI_QUOTES' SQL mode is enabled, strings can be enclosed
    only by ''' and the server interprets strings enclosed by '"'
    as identifiers.
  
  * '\' is the escape character in strings.
  
  * In SQL statements, you can access tables from different
    databases with the DB_NAME.TBL_NAME syntax.  Some SQL servers
    provide the same functionality but call this 'User space'.
    MySQL Server does not support tablespaces such as used in
    statements like this: 'CREATE TABLE ralph.my_table ... IN
    my_tablespace'.

• SQL statement syntax

  * The *note 'ANALYZE TABLE': analyze-table, *note 'CHECK TABLE':
    check-table, *note 'OPTIMIZE TABLE': optimize-table, and *note
    'REPAIR TABLE': repair-table. statements.
  
  * The *note 'CREATE DATABASE': create-database, *note 'DROP
    DATABASE': drop-database, and *note 'ALTER DATABASE':
    alter-database. statements.  See *note create-database::,
    *note drop-database::, and *note alter-database::.
  
  * The *note 'DO': do. statement.
  
  * *note 'EXPLAIN SELECT': explain. to obtain a description of
    how tables are processed by the query optimizer.
  
  * The *note 'FLUSH': flush. and *note 'RESET': reset.
    statements.
  
  * The *note 'SET': set-variable. statement.  See *note
    set-variable::.
  
  * The *note 'SHOW': show. statement.  See *note show::.  The
    information produced by many of the MySQL-specific *note
    'SHOW': show. statements can be obtained in more standard
    fashion by using *note 'SELECT': select. to query
    'INFORMATION_SCHEMA'.  See *note information-schema::.
  
  * 
    Use of *note 'LOAD DATA': load-data.  In many cases, this
    syntax is compatible with Oracle *note 'LOAD DATA': load-data.
    See *note load-data::.
  
  * Use of *note 'RENAME TABLE': rename-table.  See *note
    rename-table::.
  
  * Use of *note 'REPLACE': replace. instead of *note 'DELETE':
    delete. plus *note 'INSERT': insert.  See *note replace::.
  
  * Use of 'CHANGE COL_NAME', 'DROP COL_NAME', or *note 'DROP
    INDEX': drop-index, 'IGNORE' or 'RENAME' in *note 'ALTER
    TABLE': alter-table. statements.  Use of multiple 'ADD',
    'ALTER', 'DROP', or 'CHANGE' clauses in an *note 'ALTER
    TABLE': alter-table. statement.  See *note alter-table::.
  
  * Use of index names, indexes on a prefix of a column, and use
    of 'INDEX' or 'KEY' in *note 'CREATE TABLE': create-table.
    statements.  See *note create-table::.
  
  * Use of 'TEMPORARY' or 'IF NOT EXISTS' with *note 'CREATE
    TABLE': create-table.
  
  * Use of 'IF EXISTS' with *note 'DROP TABLE': drop-table. and
    *note 'DROP DATABASE': drop-database.
  
  * The capability of dropping multiple tables with a single *note
    'DROP TABLE': drop-table. statement.
  
  * The 'ORDER BY' and 'LIMIT' clauses of the *note 'UPDATE':
    update. and *note 'DELETE': delete. statements.
  
  * 'INSERT INTO TBL_NAME SET COL_NAME = ...' syntax.
  
  * The 'DELAYED' clause of the *note 'INSERT': insert. and *note
    'REPLACE': replace. statements.
  
  * The 'LOW_PRIORITY' clause of the *note 'INSERT': insert, *note
    'REPLACE': replace, *note 'DELETE': delete, and *note
    'UPDATE': update. statements.
  
  * Use of 'INTO OUTFILE' or 'INTO DUMPFILE' in *note 'SELECT':
    select. statements.  See *note select::.
  
  * Options such as 'STRAIGHT_JOIN' or 'SQL_SMALL_RESULT' in *note
    'SELECT': select. statements.
  
  * You don't need to name all selected columns in the 'GROUP BY'
    clause.  This gives better performance for some very specific,
    but quite normal queries.  See *note
    aggregate-functions-and-modifiers::.
  
  * You can specify 'ASC' and 'DESC' with 'GROUP BY', not just
    with 'ORDER BY'.
  
  * The ability to set variables in a statement with the ':='
    assignment operator.  See *note user-variables::.

• Data types

  * The *note 'MEDIUMINT': integer-types, *note 'SET': set, and
    *note 'ENUM': enum. data types, and the various *note 'BLOB':
    blob. and *note 'TEXT': blob. data types.
  
  * The 'AUTO_INCREMENT', 'BINARY', 'NULL', 'UNSIGNED', and
    'ZEROFILL' data type attributes.

• Functions and operators

  * To make it easier for users who migrate from other SQL
    environments, MySQL Server supports aliases for many
    functions.  For example, all string functions support both
    standard SQL syntax and ODBC syntax.
  
  * MySQL Server understands the '||' and '&&' operators to mean
    logical OR and AND, as in the C programming language.  In
    MySQL Server, '||' and 'OR' are synonyms, as are '&&' and
    'AND'.  Because of this nice syntax, MySQL Server does not
    support the standard SQL '||' operator for string
    concatenation; use 'CONCAT()' instead.  Because 'CONCAT()'
    takes any number of arguments, it is easy to convert use of
    the '||' operator to MySQL Server.
  
  * Use of 'COUNT(DISTINCT VALUE_LIST)' where VALUE_LIST has more
    than one element.
  
  * String comparisons are case-insensitive by default, with sort
    ordering determined by the collation of the current character
    set, which is 'latin1' (cp1252 West European) by default.  To
    perform case-sensitive comparisons instead, you should declare
    your columns with the 'BINARY' attribute or use the 'BINARY'
    cast, which causes comparisons to be done using the underlying
    character code values rather than a lexical ordering.
  
  * 
    The '%' operator is a synonym for 'MOD()'.  That is, 'N % M'
    is equivalent to 'MOD(N,M)'.  '%' is supported for C
    programmers and for compatibility with PostgreSQL.
  
  * The '=', '<>', '<=', '<', '>=', '>', '<<', '>>', '<=>', 'AND',
    'OR', or 'LIKE' operators may be used in expressions in the
    output column list (to the left of the 'FROM') in *note
    'SELECT': select. statements.  For example:
  
         mysql> SELECT col1=1 AND col2=2 FROM my_table;
  
  * The 'LAST_INSERT_ID()' function returns the most recent
    'AUTO_INCREMENT' value.  See *note information-functions::.
  
  * 'LIKE' is permitted on numeric values.
  
  * The 'REGEXP' and 'NOT REGEXP' extended regular expression
    operators.
  
  * 'CONCAT()' or 'CHAR()' with one argument or more than two
    arguments.  (In MySQL Server, these functions can take a
    variable number of arguments.)
  
  * The 'BIT_COUNT()', 'CASE', 'ELT()', 'FROM_DAYS()', 'FORMAT()',
    'IF()', 'PASSWORD()', 'ENCRYPT()', 'MD5()', 'ENCODE()',
    'DECODE()', 'PERIOD_ADD()', 'PERIOD_DIFF()', 'TO_DAYS()', and
    'WEEKDAY()' functions.
  
  * Use of 'TRIM()' to trim substrings.  Standard SQL supports
    removal of single characters only.
  
  * The 'GROUP BY' functions 'STD()', 'BIT_OR()', 'BIT_AND()',
    'BIT_XOR()', and 'GROUP_CONCAT()'.  See *note
    aggregate-functions-and-modifiers::.

 File: manual.info.tmp, Node: differences-from-ansi, Next: constraints, Prev: extensions-to-ansi, Up: compatibility

1.6.2 MySQL Differences from Standard SQL

• Menu:

• ansi-diff-select-into-table:: SELECT INTO TABLE Differences
• ansi-diff-update:: UPDATE Differences
• ansi-diff-foreign-keys:: FOREIGN KEY Constraint Differences

• ansi-diff-comments:: '-' as the Start of a Comment

We try to make MySQL Server follow the ANSI SQL standard and the ODBC SQL standard, but MySQL Server performs operations differently in some cases:

• There are several differences between the MySQL and standard SQL privilege systems. For example, in MySQL, privileges for a table are not automatically revoked when you delete a table. You must explicitly issue a note 'REVOKE': revoke. statement to revoke privileges for a table. For more information, see note revoke::.

• The 'CAST()' function does not support cast to note 'REAL': floating-point-types. or note 'BIGINT': integer-types. See *note cast-functions::.

 File: manual.info.tmp, Node: ansi-diff-select-into-table, Next: ansi-diff-update, Prev: differences-from-ansi, Up: differences-from-ansi

1.6.2.1 SELECT INTO TABLE Differences .....................................

MySQL Server does not support the 'SELECT ... INTO TABLE' Sybase SQL extension. Instead, MySQL Server supports the note 'INSERT INTO ... SELECT': insert-select. standard SQL syntax, which is basically the same thing. See note insert-select::. For example:

 INSERT INTO tbl_temp2 (fld_id)
     SELECT tbl_temp1.fld_order_id
     FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Alternatively, you can use note 'SELECT ... INTO OUTFILE': select-into. or note 'CREATE TABLE ... SELECT': create-table.

You can use note 'SELECT ... INTO': select. with user-defined variables. The same syntax can also be used inside stored routines using cursors and local variables. See note select-into::.

 File: manual.info.tmp, Node: ansi-diff-update, Next: ansi-diff-foreign-keys, Prev: ansi-diff-select-into-table, Up: differences-from-ansi

1.6.2.2 UPDATE Differences ..........................

If you access a column from the table to be updated in an expression, *note 'UPDATE': update. uses the current value of the column. The second assignment in the following statement sets 'col2' to the current (updated) 'col1' value, not the original 'col1' value. The result is that 'col1' and 'col2' have the same value. This behavior differs from standard SQL.

 UPDATE t1 SET col1 = col1 + 1, col2 = col1;

 File: manual.info.tmp, Node: ansi-diff-foreign-keys, Next: ansi-diff-comments, Prev: ansi-diff-update, Up: differences-from-ansi

1.6.2.3 FOREIGN KEY Constraint Differences ..........................................

The MySQL implementation of foreign key constraints differs from the SQL standard in the following key respects:

• If there are several rows in the parent table with the same referenced key value, *note 'InnoDB': innodb-storage-engine. performs a foreign key check as if the other parent rows with the same key value do not exist. For example, if you define a 'RESTRICT' type constraint, and there is a child row with several parent rows, 'InnoDB' does not permit the deletion of any of the parent rows.

• If 'ON UPDATE CASCADE' or 'ON UPDATE SET NULL' recurses to update the same table it has previously updated during the same cascade, it acts like 'RESTRICT'. This means that you cannot use self-referential 'ON UPDATE CASCADE' or 'ON UPDATE SET NULL' operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential 'ON DELETE SET NULL', on the other hand, is possible, as is a self-referential 'ON DELETE CASCADE'. Cascading operations may not be nested more than 15 levels deep.

• In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, *note 'InnoDB': innodb-storage-engine. sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.

• No storage engine, including 'InnoDB', recognizes or enforces the 'MATCH' clause used in referential-integrity constraint definitions. Use of an explicit 'MATCH' clause does not have the specified effect, and it causes 'ON DELETE' and 'ON UPDATE' clauses to be ignored. Specifying the 'MATCH' should be avoided.

  The 'MATCH' clause in the SQL standard controls how 'NULL' values in a composite (multiple-column) foreign key are handled when comparing to a primary key in the referenced table. MySQL essentially implements the semantics defined by 'MATCH SIMPLE', which permits a foreign key to be all or partially 'NULL'. In that case, a (child table) row containing such a foreign key can be inserted even though it does not match any row in the referenced (parent) table. (It is possible to implement other semantics using triggers.)

• MySQL requires that the referenced columns be indexed for performance reasons. However, MySQL does not enforce a requirement that the referenced columns be 'UNIQUE' or be declared 'NOT NULL'.

  A 'FOREIGN KEY' constraint that references a non-'UNIQUE' key is not standard SQL but rather an note 'InnoDB': innodb-storage-engine. extension. The note 'NDB': mysql-cluster. storage engine, on the other hand, requires an explicit unique key (or primary key) on any column referenced as a foreign key.

  The handling of foreign key references to nonunique keys or keys that contain 'NULL' values is not well defined for operations such as *note 'UPDATE': update. or 'DELETE CASCADE'. You are advised to use foreign keys that reference only 'UNIQUE' (including 'PRIMARY') and 'NOT NULL' keys.

• For storage engines that do not support foreign keys (such as *note 'MyISAM': myisam-storage-engine.), MySQL Server parses and ignores foreign key specifications.

• MySQL parses but ignores 'inline 'REFERENCES' specifications' (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts 'REFERENCES' clauses only when specified as part of a separate 'FOREIGN KEY' specification.

  Defining a column to use a 'REFERENCES TBL_NAME(COL_NAME)' clause has no actual effect and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table. It is important to realize when using this syntax that:

  * MySQL does not perform any sort of check to make sure that
    COL_NAME actually exists in TBL_NAME (or even that TBL_NAME
    itself exists).
  
  * MySQL does not perform any sort of action on TBL_NAME such as
    deleting rows in response to actions taken on rows in the
    table which you are defining; in other words, this syntax
    induces no 'ON DELETE' or 'ON UPDATE' behavior whatsoever.
    (Although you can write an 'ON DELETE' or 'ON UPDATE' clause
    as part of the 'REFERENCES' clause, it is also ignored.)
  
  * This syntax creates a _column_; it does *not* create any sort
    of index or key.

  You can use a column so created as a join column, as shown here:

    CREATE TABLE person (
        id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
        name CHAR(60) NOT NULL,
        PRIMARY KEY (id)
    );
  
    CREATE TABLE shirt (
        id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
        style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
        color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
        owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
        PRIMARY KEY (id)
    );
  
    INSERT INTO person VALUES (NULL, 'Antonio Paz');
  
    SELECT @last := LAST_INSERT_ID();
  
    INSERT INTO shirt VALUES
    (NULL, 'polo', 'blue', @last),
    (NULL, 'dress', 'white', @last),
    (NULL, 't-shirt', 'blue', @last);
  
    INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
  
    SELECT @last := LAST_INSERT_ID();
  
    INSERT INTO shirt VALUES
    (NULL, 'dress', 'orange', @last),
    (NULL, 'polo', 'red', @last),
    (NULL, 'dress', 'blue', @last),
    (NULL, 't-shirt', 'white', @last);
  
    SELECT * FROM person;
    +----+---------------------+
    | id | name                |
    +----+---------------------+
    |  1 | Antonio Paz         |
    |  2 | Lilliana Angelovska |
    +----+---------------------+
  
    SELECT * FROM shirt;
    +----+---------+--------+-------+
    | id | style   | color  | owner |
    +----+---------+--------+-------+
    |  1 | polo    | blue   |     1 |
    |  2 | dress   | white  |     1 |
    |  3 | t-shirt | blue   |     1 |
    |  4 | dress   | orange |     2 |
    |  5 | polo    | red    |     2 |
    |  6 | dress   | blue   |     2 |
    |  7 | t-shirt | white  |     2 |
    +----+---------+--------+-------+
  
    SELECT s.* FROM person p INNER JOIN shirt s
       ON s.owner = p.id
     WHERE p.name LIKE 'Lilliana%'
       AND s.color <> 'white';
  
    +----+-------+--------+-------+
    | id | style | color  | owner |
    +----+-------+--------+-------+
    |  4 | dress | orange |     2 |
    |  5 | polo  | red    |     2 |
    |  6 | dress | blue   |     2 |
    +----+-------+--------+-------+

  When used in this fashion, the 'REFERENCES' clause is not displayed in the output of note 'SHOW CREATE TABLE': show-create-table. or note 'DESCRIBE': describe.:

    SHOW CREATE TABLE shirt\G
    *************************** 1. row ***************************
    Table: shirt
    Create Table: CREATE TABLE `shirt` (
    `id` smallint(5) unsigned NOT NULL auto_increment,
    `style` enum('t-shirt','polo','dress') NOT NULL,
    `color` enum('red','blue','orange','white','black') NOT NULL,
    `owner` smallint(5) unsigned NOT NULL,
    PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

For information about foreign key constraints, see *note create-table-foreign-keys::.

 File: manual.info.tmp, Node: ansi-diff-comments, Prev: ansi-diff-foreign-keys, Up: differences-from-ansi

1.6.2.4 '-' as the Start of a Comment .....................................

Standard SQL uses the C syntax '/* this is a comment /' for comments, and MySQL Server supports this syntax as well. MySQL also support extensions to this syntax that enable MySQL-specific SQL to be embedded in the comment; see note comments::.

MySQL Server also uses '#' as the start comment character. This is nonstandard.

Standard SQL also uses ''--'' as a start-comment sequence. MySQL Server supports a variant of the '--' comment style; the '--' start-comment sequence is accepted as such, but must be followed by a whitespace character such as a space or newline. The space is intended to prevent problems with generated SQL queries that use constructs such as the following, which updates the balance to reflect a charge:

 UPDATE account SET balance=balance-charge
 WHERE account_id=user_id

Consider what happens when 'charge' has a negative value such as '-1', which might be the case when an amount is credited to the account. In this case, the generated statement looks like this:

 UPDATE account SET balance=balance--1
 WHERE account_id=5752;

'balance--1' is valid standard SQL, but '--' is interpreted as the start of a comment, and part of the expression is discarded. The result is a statement that has a completely different meaning than intended:

 UPDATE account SET balance=balance
 WHERE account_id=5752;

This statement produces no change in value at all. To keep this from happening, MySQL requires a whitespace character following the '--' for it to be recognized as a start-comment sequence in MySQL Server, so that an expression such as 'balance--1' is always safe to use.

 File: manual.info.tmp, Node: constraints, Prev: differences-from-ansi, Up: compatibility

1.6.3 How MySQL Deals with Constraints

• Menu:

• constraint-primary-key:: PRIMARY KEY and UNIQUE Index Constraints
• constraint-foreign-key:: FOREIGN KEY Constraints
• constraint-invalid-data:: Constraints on Invalid Data

• constraint-enum:: ENUM and SET Constraints

MySQL enables you to work both with transactional tables that permit rollback and with nontransactional tables that do not. Because of this, constraint handling is a bit different in MySQL than in other DBMSs. We must handle the case when you have inserted or updated a lot of rows in a nontransactional table for which changes cannot be rolled back when an error occurs.

The basic philosophy is that MySQL Server tries to produce an error for anything that it can detect while parsing a statement to be executed, and tries to recover from any errors that occur while executing the statement. We do this in most cases, but not yet for all.

The options MySQL has when an error occurs are to stop the statement in the middle or to recover as well as possible from the problem and continue. By default, the server follows the latter course. This means, for example, that the server may coerce invalid values to the closest valid values.

Several SQL mode options are available to provide greater control over handling of bad data values and whether to continue statement execution or abort when errors occur. Using these options, you can configure MySQL Server to act in a more traditional fashion that is like other DBMSs that reject improper input. The SQL mode can be set globally at server startup to affect all clients. Individual clients can set the SQL mode at runtime, which enables each client to select the behavior most appropriate for its requirements. See *note sql-mode::.

The following sections describe how MySQL Server handles different types of constraints.

 File: manual.info.tmp, Node: constraint-primary-key, Next: constraint-foreign-key, Prev: constraints, Up: constraints

1.6.3.1 PRIMARY KEY and UNIQUE Index Constraints ................................................

Normally, errors occur for data-change statements (such as note 'INSERT': insert. or note 'UPDATE': update.) that would violate primary-key, unique-key, or foreign-key constraints. If you are using a transactional storage engine such as 'InnoDB', MySQL automatically rolls back the statement. If you are using a nontransactional storage engine, MySQL stops processing the statement at the row for which the error occurred and leaves any remaining rows unprocessed.

MySQL supports an 'IGNORE' keyword for note 'INSERT': insert, note 'UPDATE': update, and so forth. If you use it, MySQL ignores primary-key or unique-key violations and continues processing with the next row. See the section for the statement that you are using (note insert::, note update::, and so forth).

You can get information about the number of rows actually inserted or updated with the 'mysql_info()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-info.html) C API function. You can also use the note 'SHOW WARNINGS': show-warnings. statement. See mysql_info() (https://dev.mysql.com/doc/c-api/5.7/en/mysql-info.html), and note show-warnings::.

'InnoDB' and 'NDB' tables support foreign keys. See *note constraint-foreign-key::.

 File: manual.info.tmp, Node: constraint-foreign-key, Next: constraint-invalid-data, Prev: constraint-primary-key, Up: constraints

1.6.3.2 FOREIGN KEY Constraints ...............................

Foreign keys let you cross-reference related data across tables, and foreign key constraints help keep this spread-out data consistent.

MySQL supports 'ON UPDATE' and 'ON DELETE' foreign key references in note 'CREATE TABLE': create-table. and note 'ALTER TABLE': alter-table. statements. The available referential actions are 'RESTRICT' (the default), 'CASCADE', 'SET NULL', and 'NO ACTION'.

'SET DEFAULT' is also supported by the MySQL Server but is currently rejected as invalid by note 'InnoDB': innodb-storage-engine. Since MySQL does not support deferred constraint checking, 'NO ACTION' is treated as 'RESTRICT'. For the exact syntax supported by MySQL for foreign keys, see note create-table-foreign-keys::.

'MATCH FULL', 'MATCH PARTIAL', and 'MATCH SIMPLE' are allowed, but their use should be avoided, as they cause the MySQL Server to ignore any 'ON DELETE' or 'ON UPDATE' clause used in the same statement. 'MATCH' options do not have any other effect in MySQL, which in effect enforces 'MATCH SIMPLE' semantics full-time.

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

You can obtain information about foreign keys from the Information Schema *note 'KEY_COLUMN_USAGE': information-schema-key-column-usage-table. table. An example of a query against this table is shown here:

 mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
      > FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
      > WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
 +--------------+---------------+-------------+-----------------+
 | TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |
 +--------------+---------------+-------------+-----------------+
 | fk1          | myuser        | myuser_id   | f               |
 | fk1          | product_order | customer_id | f2              |
 | fk1          | product_order | product_id  | f1              |
 +--------------+---------------+-------------+-----------------+
 3 rows in set (0.01 sec)

Information about foreign keys on 'InnoDB' tables can also be found in the note 'INNODB_SYS_FOREIGN': information-schema-innodb-sys-foreign-table. and note 'INNODB_SYS_FOREIGN_COLS': information-schema-innodb-sys-foreign-cols-table. tables, in the 'INFORMATION_SCHEMA' database.

'InnoDB' and 'NDB' tables support foreign keys.

 File: manual.info.tmp, Node: constraint-invalid-data, Next: constraint-enum, Prev: constraint-foreign-key, Up: constraints

1.6.3.3 Constraints on Invalid Data ...................................

MySQL 5.7.5 and later uses strict SQL mode by default, which treats invalid values such that the server rejects them and aborts the statement in which they occur (see *note sql-mode::). Previously, MySQL was much more forgiving of incorrect values used in data entry; this now requires disabling of strict mode, which is not recommended. The remainder of this section discusses the old behavior followed by MySQL when strict mode has been disabled.

If you are not using strict mode, then whenever you insert an 'incorrect' value into a column, such as a 'NULL' into a 'NOT NULL' column or a too-large numeric value into a numeric column, MySQL sets the column to the 'best possible value' instead of producing an error: The following rules describe in more detail how this works:

• If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value.

• For strings, MySQL stores either the empty string or as much of the string as can be stored in the column.

• If you try to store a string that does not start with a number into a numeric column, MySQL Server stores 0.

• Invalid values for note 'ENUM': enum. and note 'SET': set. columns are handled as described in *note constraint-enum::.

• MySQL permits you to store certain incorrect date values into note 'DATE': datetime. and note 'DATETIME': datetime. columns (such as ''2000-02-31'' or ''2000-02-00''). In this case, when an application has not enabled strict SQL mode, it up to the application to validate the dates before storing them. If MySQL can store a date value and retrieve exactly the same value, MySQL stores it as given. If the date is totally wrong (outside the server's ability to store it), the special 'zero' date value ''0000-00-00'' is stored in the column instead.

• If you try to store 'NULL' into a column that does not take 'NULL' values, an error occurs for single-row note 'INSERT': insert. statements. For multiple-row note 'INSERT': insert. statements or for note 'INSERT INTO ... SELECT': insert-select. statements, MySQL Server stores the implicit default value for the column data type. In general, this is '0' for numeric types, the empty string ('''') for string types, and the 'zero' value for date and time types. Implicit default values are discussed in note data-type-defaults::.

• If an *note 'INSERT': insert. statement specifies no value for a column, MySQL inserts its default value if the column definition includes an explicit 'DEFAULT' clause. If the definition has no such 'DEFAULT' clause, MySQL inserts the implicit default value for the column data type.

The reason for using the preceding rules when strict mode is not in effect is that we cannot check these conditions until the statement has begun executing. We cannot just roll back if we encounter a problem after updating a few rows, because the storage engine may not support rollback. The option of terminating the statement is not that good; in this case, the update would be 'half done,' which is probably the worst possible scenario. In this case, it is better to 'do the best you can' and then continue as if nothing happened.

You can select stricter treatment of input values by using the 'STRICT_TRANS_TABLES' or 'STRICT_ALL_TABLES' SQL modes:

 SET sql_mode = 'STRICT_TRANS_TABLES';
 SET sql_mode = 'STRICT_ALL_TABLES';

'STRICT_TRANS_TABLES' enables strict mode for transactional storage engines, and also to some extent for nontransactional engines. It works like this:

• For transactional storage engines, bad data values occurring anywhere in a statement cause the statement to abort and roll back.

• For nontransactional storage engines, a statement aborts if the error occurs in the first row to be inserted or updated. (When the error occurs in the first row, the statement can be aborted to leave the table unchanged, just as for a transactional table.) Errors in rows after the first do not abort the statement, because the table has already been changed by the first row. Instead, bad data values are adjusted and result in warnings rather than errors. In other words, with 'STRICT_TRANS_TABLES', a wrong value causes MySQL to roll back all updates done so far, if that can be done without changing the table. But once the table has been changed, further errors result in adjustments and warnings.

For even stricter checking, enable 'STRICT_ALL_TABLES'. This is the same as 'STRICT_TRANS_TABLES' except that for nontransactional storage engines, errors abort the statement even for bad data in rows following the first row. This means that if an error occurs partway through a multiple-row insert or update for a nontransactional table, a partial update results. Earlier rows are inserted or updated, but those from the point of the error on are not. To avoid this for nontransactional tables, either use single-row statements or else use 'STRICT_TRANS_TABLES' if conversion warnings rather than errors are acceptable. To avoid problems in the first place, do not use MySQL to check column content. It is safest (and often faster) to let the application ensure that it passes only valid values to the database.

With either of the strict mode options, you can cause errors to be treated as warnings by using note 'INSERT IGNORE': insert. or 'UPDATE IGNORE' rather than note 'INSERT': insert. or *note 'UPDATE': update. without 'IGNORE'.

 File: manual.info.tmp, Node: constraint-enum, Prev: constraint-invalid-data, Up: constraints

1.6.3.4 ENUM and SET Constraints ................................

note 'ENUM': enum. and note 'SET': set. columns provide an efficient way to define columns that can contain only a given set of values. See note enum::, and note set::.

Unless strict mode is disabled (not recommended, but see note sql-mode::), the definition of a note 'ENUM': enum. or *note 'SET': set. column acts as a constraint on values entered into the column. An error occurs for values that do not satisfy these conditions:

• An note 'ENUM': enum. value must be one of those listed in the column definition, or the internal numeric equivalent thereof. The value cannot be the error value (that is, 0 or the empty string). For a column defined as note 'ENUM('a','b','c')': enum, values such as '''', ''d'', or ''ax'' are invalid and are rejected.

• A note 'SET': set. value must be the empty string or a value consisting only of the values listed in the column definition separated by commas. For a column defined as note 'SET('a','b','c')': set, values such as ''d'' or ''a,b,c,d'' are invalid and are rejected.

Errors for invalid values can be suppressed in strict mode if you use note 'INSERT IGNORE': insert. or 'UPDATE IGNORE'. In this case, a warning is generated rather than an error. For note 'ENUM': enum, the value is inserted as the error member ('0'). For *note 'SET': set, the value is inserted as given except that any invalid substrings are deleted. For example, ''a,x,b,y'' results in a value of ''a,b''.

 File: manual.info.tmp, Node: installing, Next: tutorial, Prev: introduction, Up: Top

2 Installing and Upgrading MySQL ********************************

• Menu:

• general-installation-issues:: General Installation Guidance
• binary-installation:: Installing MySQL on Unix/Linux Using Generic Binaries
• windows-installation:: Installing MySQL on Microsoft Windows
• macos-installation:: Installing MySQL on macOS
• linux-installation:: Installing MySQL on Linux
• uln-installation:: Installing MySQL Using Unbreakable Linux Network (ULN)
• solaris-installation:: Installing MySQL on Solaris
• source-installation:: Installing MySQL from Source
• postinstallation:: Postinstallation Setup and Testing
• upgrading:: Upgrading MySQL
• downgrading:: Downgrading MySQL

• perl-support:: Perl Installation Notes

This chapter describes how to obtain and install MySQL. A summary of the procedure follows and later sections provide the details. If you plan to upgrade an existing version of MySQL to a newer version rather than install MySQL for the first time, see *note upgrading::, for information about upgrade procedures and about issues that you should consider before upgrading.

If you are interested in migrating to MySQL from another database system, see *note faqs-migration::, which contains answers to some common questions concerning migration issues.

Installation of MySQL generally follows the steps outlined here:

1. Determine whether MySQL runs and is supported on your platform.

   Please note that not all platforms are equally suitable for running MySQL, and that not all platforms on which MySQL is known to run are officially supported by Oracle Corporation. For information about those platforms that are officially supported, see https://www.mysql.com/support/supportedplatforms/database.html on the MySQL website.

2. Choose which distribution to install.

   Several versions of MySQL are available, and most are available in several distribution formats. You can choose from pre-packaged distributions containing binary (precompiled) programs or source code. When in doubt, use a binary distribution. Oracle also provides access to the MySQL source code for those who want to see recent developments and test new code. To determine which version and type of distribution you should use, see *note which-version::.

3. Download the distribution that you want to install.

   For instructions, see note getting-mysql::. To verify the integrity of the distribution, use the instructions in note verifying-package-integrity::.

4. Install the distribution.

   To install MySQL from a binary distribution, use the instructions in *note binary-installation::. Alternatively, use the Secure Deployment Guide (https://dev.mysql.com/doc/mysql-secure-deployment-guide/5.7/en/), which provides procedures for deploying a generic binary distribution of MySQL Enterprise Edition Server with features for managing the security of your MySQL installation.

   To install MySQL from a source distribution or from the current development source tree, use the instructions in *note source-installation::.

5. Perform any necessary postinstallation setup.

   After installing MySQL, see note postinstallation:: for information about making sure the MySQL server is working properly. Also refer to the information provided in note default-privileges::. This section describes how to secure the initial MySQL 'root' user account, which has no password until you assign one. The section applies whether you install MySQL using a binary or source distribution.

6. If you want to run the MySQL benchmark scripts, Perl support for MySQL must be available. See *note perl-support::.

Instructions for installing MySQL on different platforms and environments is available on a platform by platform basis:

• Unix, Linux

  For instructions on installing MySQL on most Linux and Unix platforms using a generic binary (for example, a '.tar.gz' package), see *note binary-installation::.

  For information on building MySQL entirely from the source code distributions or the source code repositories, see *note source-installation::

  For specific platform help on installation, configuration, and building from source see the corresponding platform section:

  * Linux, including notes on distribution specific methods, see
    *note linux-installation::.
  
  * Solaris, including PKG and IPS formats, see *note
    solaris-installation::.
  
  * IBM AIX, see *note solaris-installation::.

• Microsoft Windows

  For instructions on installing MySQL on Microsoft Windows, using either the MySQL Installer or Zipped binary, see *note windows-installation::.

  For details and instructions on building MySQL from source code using Microsoft Visual Studio, see *note source-installation::.

• macOS

  For installation on macOS, including using both the binary package and native PKG formats, see *note macos-installation::.

  For information on making use of an macOS Launch Daemon to automatically start and stop MySQL, see *note macos-installation-launchd::.

  For information on the MySQL Preference Pane, see *note macos-installation-prefpane::.

 File: manual.info.tmp, Node: general-installation-issues, Next: binary-installation, Prev: installing, Up: installing