MySQL: What is SQL mode and why we use it

Problem

If in an application, if we execute sql_mode=mysql4.0 as a first query in my application but using MySQL 5.1 in my application then what will happen?

Will my system use MySQL 4.0 or MySQL 5.1 version features? What is

SQL mode in a RDBMS? Why we should use this?

On which basis, we should set its value?

Solution

MySQL's sql_mode setting allows you to enforce or relax ANSI standards for SQL processing.

In your case, MySQL will regress in behavior and results. In what sense ? MySQL 5.1 version features will stay, but how the SQL is processing changes. Here is proof:

MySQL 5.0 first introduced Stored Procedures to the MySQL realm.

http://mike.kruckenberg.com/archives/2005/04/mysql_50_in_dep.html

https://discussions.vtiger.com/index.php?p=/discussion/6276/vtiger-crm-mysql-5-stored-procedures/p1

http://link.springer.com/chapter/10.1007/978-1-4302-0048-2_9#page-1

MySQL 4.x never had Stored Procedures.

Now, let's take a look at the physical home of MySQL 5.x Stored Procedures, mysql.proc:

```
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| db | char(64) | NO | PRI | | |
| name | char(64) | NO | PRI | | |
| type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| specific_name | char(64)

Code Snippets

+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| Field                | Type                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | Null | Key | Default             | Extra                       |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| db                   | char(64)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | NO   | PRI |                     |                             |
| name                 | char(64)

sql_mode
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED',
'ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL',
'ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS',
'MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES',
'STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE',
'INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',
'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')

Context

StackExchange Database Administrators Q#57403, answer score: 8

Revisions (0)

No revisions yet.