MySQL Database learning



Home / Client Server Commands / What is sql mode in MySQL and how can we set it?
Share this job:


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

SQL mode (sql_mode) is a MySQL system variable. By means of this variable 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:





Client Server Commands related other questions

How to check version of the running MySQL Server?

You can check by runing this querySHOW GLOBAL VARIABLES LIKE "VERSION";SELECT VERSION(); ...

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

mysqld is the MySQL database server program, which manages access to the actual databases on disk, in memory etc. Whereas mysqladmin, mysqldump, mysqlimport and mysqlcheck are MySQL client programs. mysqla ...

What is mysql client program and how to run it?

mysql is one of the MySQL client programs. It is a command-line program that acts as a text-based front end for the MySQL Server. It\'s used for issuing queries and viewing the results intera ...

What is --compress or -C?

This is one of the MySQL connection options. This option causes data sent between the client and the server to be compressed before transmission and uncompressed upon receipt. In profit, it reduces the numbe ...

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

To connect with MySQL server, client programs may be required to invoke connection options while making connection. However, the options can also be placed in file(s) as an alternative to specifying options ...

How to check the MySQL running mode?

Use one of the following two examples, each will provide current mode info:SELECT @@sql_mode;orSHOW VARIABLES LIKE \'sql_mode\'; ...

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

SQL mode (sql_mode) is a MySQL system variable. By means of this variable the MySQL Server SQL mode is controlled. Many operational characteristics of MySQL Server can be configured by setting the SQL mode. ...

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

MySQL allows several queries to be entered on a single input line and uses the terminators to distinguish where each query ends. You may use any of several terminators to end a statement. Two terminators are ...

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

You can use the -e or --execute option to a statement directly from the command line. Take an example, you want to see all user data from user table of mysql database. My MySQL username and password is root ...

How to cancel the statement in mysql client program?

If you want to cancel the statement that you are composing, just enter \\c mysql will cancel that and return you to a new mysql> prompt. Take an example like below:mysql> SELECT Name, Population ...

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

The mysql client program enables the user to send queries to the MySQL server and receive their results. It can be used interactively or it can read query input from a file in batch mode.Interactive mode ...

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

mysql understands the standard command-line options for specifying connection parameters.  So to invoke mysql interactively from the command line, you have to specify any necessary connection parameters ...

How to run SQL script files in mysql client?

One way to process a script file is by executing it with a SOURCE command from within mysql:mysql> SOURCE input_fileThe file must be located on the client host where you\'re running mysql ...

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

If a statement in a script file fails with an error, mysql ignores the rest of the file. To execute the entire file regardless of whether errors occur, invoke mysql with the --force or -f option. ...

What does STATUS command in mysql client?

STATUS is mysql command, which displays information about the current connection to the server, as well as status information about the server itself. Here is what a status display might look ...

How to know MySQL system variables?

MySQL allows to show its system variables as well as session variables (user level variables).  To get MySQL system level variables, you may run the following query in any MySQL client tool like Query Br ...

What does MySQL use its port by default?

MySQL Server uses port 3306 by default. Further, you can also check the current port number being used by your MySQL Server. For this, you may run the following query:SHOW VARIABLES LIKE \"port\"; ...

What are MySQL client programs?

In MySQL Client programs are programs that can be used for communicating with the server to manipulate the information in the databases that the server manages. MySQL AB provides several client programs. The ...

What are MySQL non-client utilities?

In MySQL non-client utilities are programs that act independently of the server. They do not operate by first establishing a connection to the server. myisamchk is an example. It performs ta ...

What does --safe-updates option in mysql?

It's possible to inadvertently issue statements that modify many rows in a table or that return extremely large result sets. The --safe-updates option helps prevent these problems. The option is particularly ...