Advertisement: Jobs in UAE, Saudi Arabia, Qatar, Pakistan, Bahrain & other regions. Click here to find one for you.

MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
Powered by MySQL
 
Home / Client Server Commands / Question No: 85

What is sql mode in MySQL and how can we set it?

Answer No: 85

SQL mode (sql_mode) is a MySQL system variable. By means of this varriable the MySQL Server SQL mode is controlled. Many operational characteristics of MySQL Server can be configured by setting the SQL mode. 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 behaviors relating to standard SQL conformance, or provide better compatibility with other database systems. By default, the server uses a sql_mode value of  ' '  (the empty string), which enables no restrictions. Thus, the server operates in forgiving mode (non-strict mode) by default. In non-strict mode, the MySQL server converts erroneous input values to the closest legal values (as determined from column definitions) and continues on its way. For example, if you attempt to store a negative value into an UNSIGNED column, MySQL converts it to zero, which is the nearest legal value for the column.

The SQL mode is controlled by means of the sql_mode system variable. To assign a value to this variable, SET statement is used. 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. Here are some examples:

To clear the SQL mode or to set non-strict mode:

SET sql_mode = '';

The most general means of enabling input value restrictions is by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES modes:

SET sql_mode = STRICT_TRANS_TABLES;
SET sql_mode = STRICT_ALL_TABLES;

The term "strict mode" refers collectively to both of these modes. They prevent entry of invalid values such as those that are out of range, or NULL specified for NOT NULL columns.

To set the SQL mode using a single mode value:

SET sql_mode = ANSI_QUOTES;
SET sql_mode = TRADITIONAL;

The TRADITIONAL mode, enables strict mode plus other restrictions on date checking and division by zero. Setting the sql_mode system variable to TRADITIONAL causes MySQL to act like more traditional database servers in its input data handling.  If you want your MySQL server to be as restrictive as possible about input data checking, the simplest way to achieve this is to enable TRADITIONAL mode rather than a list of individual more-specific modes.

To 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:

SELECT @@sql_mode;

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:

SET sql_mode=TRADITIONAL;
SELECT @@sql_mode;

If you want relaxed date checking that requires only that month and day values be in the respective ranges of 1 to 12 and 1 to 31, enable the ALLOW_INVALID_DATES SQL mode value:

SET sql_mode = ALLOW_INVALID_DATES;

You can use ALLOW_INVALID_DATES for relaxed date checking even in strict mode:

SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

The following list briefly describes some of the values:

  • ANSI_QUOTES
    This mode causes the double quote character ('"') to be interpreted as an identifier-quoting character rather than as a string-quoting character.
  • IGNORE_SPACE
    By default, functions must be written with no space between the function name and the following parenthesis. Enabling this mode causes the server to ignore spaces after function names. This allows spaces to appear between the name and the parenthesis, but also causes function names to be reserved words.
  • ERROR_FOR_DIVISION_BY_ZERO
    By default, division by zero produces a result of NULL and is not treated specially. Enabling this mode causes division by zero in the context of inserting data into tables to produce a warning, or an error in strict mode.
  • STRICT_TRANS_TABLES, STRICT_ALL_TABLES
    These values enable "strict mode" which imposes certain restrictions on what values are acceptable as database input. By default, MySQL is forgiving about accepting values that are missing, out of range, or malformed. Enabling strict mode causes bad values to be treated as erroneous. STRICT_TRANS_TABLES enables strict mode for transactional tables, and STRICT_ALL_TABLES enables strict mode for all tables.
  • TRADITIONAL
    This is a composite mode that enables both strict modes plus several additional restrictions on acceptance of input data.
  • ANSI
    This is a composite mode that causes MySQL Server to be more "ANSI-like." That is, it enables behaviors that are more like standard SQL, such as ANSI_QUOTES (described earlier) and PIPES_AS_CONCAT, which causes || to be treated as the string concatenation operator rather than as logical OR.

Related MySQL FAQs to the Above FAQ

What-are-MySQL-client-programs What are MySQL client programs?

What-are-MySQL-non-client-utilities What are MySQL non-client utilities?

What-is-mysql-client-program-and-how-to-run-it What is mysql client program and how to run it?

How-to-connect-mysql-client-program-with-MySQL-Server-from-the-command-line How to connect mysql client program with MySQL Server from the command line?

What-is-difference-among-mysqld-mysqladmin-mysqldump-mysqlimport-and-mysqlcheck What is difference among mysqld, mysqladmin, mysqldump, mysqlimport and mysqlcheck?

How-to-check-version-of-the-running-MySQL-Server How to check version of the running MySQL Server?

What-is----compress-or--C What is --compress or -C?

What-is-difference-among---defaults-file---defaults-extra-file-and---no-defaults What is difference among --defaults-file, --defaults-extra-file and --no-defaults?

What-is-difference-between-g-and-G-characters-in-mysql-client-program What is difference between \g and \G characters in mysql client program?

How-to-execute-statement(s)-directly-from-the-command-line How to execute statement(s) directly from the command line?

How-to-cancel-the-statement-in-mysql-client-program How to cancel the statement in mysql client program?

What-is-difference-between-Interactive-Mode-and-Batch-Mode-of-mysql-client What is difference between Interactive Mode and Batch Mode of mysql client?

How-to-run-SQL-script-files-in-mysql-client How to run SQL script files in mysql client?

How-to-execute-the-entire-script-file-regardless-of-errors-occurrence How to execute the entire script file regardless of errors occurrence?

What-does-STATUS-command-in-mysql-client What does STATUS command in mysql client?

How-to-know-MySQL-system-variables How to know MySQL system variables?

What-does-MySQL-use-its-port-by-default What does MySQL use its port by default?

What-does---safe-updates-option-in-mysql What does --safe-updates option in mysql?

How-to-check-the-MySQL-running-mode How to check the MySQL running mode?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Sulata iSoft - Limitless Solutions | Limitless Job Opportunities - Careermidway.com

© 2007 - 2014  www.mysqlfaqs.net
All rights reserved. The site is owned and operated by Mr. Javed Bhatti - Pakistan.