Tutorial for MySQL data validation

Though most validation of data is generally controlled by applications, there are still ample situations where data is manipulated directly on the database. MySQL validation basically depends on the SQL modes of the server. Both INSERT and UPDATE statements can be run with the IGNORE option to allow for certain type of validation errors to go through.

What are server SQL modes?

Server SQL modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. The MySQL Server applies these modes individually to different clients.

Many operational characteristics of MySQL Server can be configured by setting the SQL mode. This mode consists of optional values that each controls some aspect of query processing. By setting the SQL mode appropriately, a client program can instruct the server how strict or forgiving to be about accepting input data, enable or disable behaviours relating to standard SQL conformance, or provide better compatibility with other database systems. It’s necessary to understand how to do this because references to the SQL mode occur throughout this study guide.

The SQL mode is controlled by means of the sql_mode system variable. To assign a value to this variable, use a SET statement. The value should be an empty string, or one or more mode names separated by commas. If the value is empty or contains more than one mode name, it must be quoted. If the value contains a single mode name, quoting is optional. SQL mode values are not case sensitive, although this study guide always writes them in uppercase. Here are some examples:

• Clear the SQL mode:

SET sql_mode = ”;

• Set the SQL mode using a single mode value:

SET sql_mode = ANSI_QUOTES;

SET sql_mode = ‘TRADITIONAL’;

• Set the SQL mode using multiple mode names:

SET sql_mode = ‘IGNORE_SPACE,ANSI_QUOTES’;

SET sql_mode = ‘STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO’;

To check the current sql_mode setting, select its value like this:

mysql> SELECT @@sql_mode;

+———————————————-+

| @@sql_mode                                   |

+———————————————-+

| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO |

+———————————————-+

Some SQL mode values are composite modes that actually enable a set of modes. Values in this category include ANSI and TRADITIONAL. To see which mode values a composite mode consists of, retrieve the value after setting it:

mysql> SET sql_mode=’TRADITIONAL”;

Query OK, 0 rows affected (0.07 sec)

mysql> SELECT @@sql_mode\G

*************************** 1. row ***************************

@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,

NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,

NO_AUTO_CREATE_USER

1 row in set (0.03 sec)

The following list describes all supported modes:

• ALLOW_INVALID_DATES

Don’t do full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

The server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable ALLOW_INVALID_DATES.

• ANSI_QUOTES

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

• ERROR_FOR_DIVISION_BY_ZERO

Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL.

• HIGH_NOT_PRECEDENCE

The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behaviour can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.

mysql> SET sql_mode = '';

mysql> SELECT NOT 1 BETWEEN -5 AND 5;

        -> 0

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';

mysql> SELECT NOT 1 BETWEEN -5 AND 5;

        -> 1

• IGNORE_SPACE

Allow spaces between a function name and the “(” character. This causes built-in function names to be treated as reserved words. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

mysql> CREATE TABLE count (i INT);

ERROR 1064 (42000): You have an error in your SQL syntax

The table name should be quoted:

mysql> CREATE TABLE `count` (i INT);

Query OK, 0 rows affected (0.00 sec)

The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always allowable to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

• NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.

• NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behaviour for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table’s AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

• NO_BACKSLASH_ESCAPES

Disable the use of the backslash character (“\”) as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other.

• NO_DIR_IN_CREATE

When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.

• NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

Up through MySQL 5.1.11, with NO_ENGINE_SUBSTITUTION disabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable for any reason (whether disabled or invalid).

As of MySQL 5.1.12, storage engines can be pluggable at runtime, so the distinction between disabled and invalid no longer applies. All unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

• NO_FIELD_OPTIONS

Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

• NO_KEY_OPTIONS

Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

• NO_TABLE_OPTIONS

Does not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

• NO_UNSIGNED_SUBTRACTION

In integer subtraction operations, do not mark the result as UNSIGNED if one of the operands is unsigned. In other words, the result of a subtraction is always signed whenever this mode is in effect, even if one of the operands is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 in table t2:

mysql> SET SQL_MODE='';

mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);

mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;

mysql> DESCRIBE t1;

+-------+---------------------+------+-----+---------+-------+

| Field | Type                | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| c2    | bigint(21) unsigned |      |     | 0       |       |

+-------+---------------------+------+-----+---------+-------+

mysql> SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';

mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;

mysql> DESCRIBE t2;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| c2    | bigint(21) |      |     | 0       |       |

+-------+------------+------+-----+---------+-------+

Note that this means that BIGINT UNSIGNED is not 100% usable in all contexts.

mysql> SET SQL_MODE = '';

mysql> SELECT CAST(0 AS UNSIGNED) - 1;

+-------------------------+

| CAST(0 AS UNSIGNED) - 1 |

+-------------------------+

|    18446744073709551615 |

+-------------------------+

mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';

mysql> SELECT CAST(0 AS UNSIGNED) - 1;

+-------------------------+

| CAST(0 AS UNSIGNED) - 1 |

+-------------------------+

|                      -1 |

+-------------------------+

• NO_ZERO_DATE

In strict mode, don’t allow '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated.

• NO_ZERO_IN_DATE

In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If used with the IGNORE option, MySQL inserts a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated.

• ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to no aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

As of MySQL 5.1.11, this mode also restricts references to no aggregated columns in the HAVING clause that are not named in the GROUP BY clause.

• PAD_CHAR_TO_FULL_LENGTH

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval. This mode was added in MySQL 5.1.20.

mysql> CREATE TABLE t1 (c1 CHAR(10));

Query OK, 0 rows affected (0.37 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');

Query OK, 1 row affected (0.01 sec)

mysql> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;

+------+-----------------+

| c1   | CHAR_LENGTH(c1) |

+------+-----------------+

| xy   |               2 |

+------+-----------------+

1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;

+------------+-----------------+

| c1         | CHAR_LENGTH(c1) |

+------------+-----------------+

| xy         |              10 |

+------------+-----------------+

1 row in set (0.00 sec)

• PIPES_AS_CONCAT

Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.

• REAL_AS_FLOAT

Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

• STRICT_ALL_TABLES

Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows.

• STRICT_TRANS_TABLES

Enable strict mode for transactional storage engines, and when possible for no transactional storage engines. Additional details follow.

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

For no transactional tables, the behaviour is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

• For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it is best to use single-row statements because these can be aborted without changing the table.
• For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement.

Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero months or day parts such as '2004-04-00' or “zero” dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behaviour by using INSERT IGNORE or UPDATE IGNORE.