Detailed explanation of viewing and setting SQL Mode in MySQL

Viewing and Setting SQL Mode in MySQL

MySQL can run in different modes, and can run different modes in different scenarios, which mainly depends on the value of the system variable sql_mode. This article mainly introduces the viewing and setting of this value, mainly on Mac systems.

The meaning and function of each mode can be easily found on the Internet, so this article will not introduce them.

It can be divided into three levels according to the effective area and time: session level, global level, and configuration (permanently effective) level.

Session Level:

Check-

select @@session.sql_mode;

Revise-

set @@session.sql_mode='xx_mode'
set session sql_mode='xx_mode'

session can be omitted, the default session is only valid for the current session

Global level:

Check-

select @@global.sql_mode;

Revise-

set global sql_mode='xx_mode';
set @@global.sql_mode='xx_mode';

It requires advanced permissions and is only effective for the next connection. It does not affect the current session (tested). It will become invalid after MySQL is restarted because the corresponding value in the configuration file will be re-read when MySQL is restarted. If you want it to be permanent, you need to modify the value in the configuration file.

Configuration modification (permanent):

Open vi /etc/my.cnf

Add below

[mysqld]
sql-mode = "xx_mode"

Note: [mysqld] must be added, and there is a "-" in the middle of sql-mode, not an underscore.

Save and exit, restart the server, and the changes will take effect permanently.

Because there is no configuration file for MySQL installed on Mac, you need to add it manually.

ps

Finally, let me add a little extra: starting, stopping, and restarting MySQL on Mac.

There are two main ways.

One is to click the MySQL panel corresponding to "System Preferences" to achieve management.

The second is the command line method.

MySQL-related execution scripts, the two most commonly used are:

/usr/local/mysql/support-files/mysql.server
/usr/local/mysql/bin/mysql

mysql.server controls the start and stop of the server.

mysql.server start|stop|restart|status

mysql is mainly used to connect to the server.

mysql -uroot -p **** -h **** -D **

Some require sudo permissions, and the relevant paths can be added to the environment variables to simplify writing. As for how to add them, I will not introduce them here.

Knowledge point expansion:

Strict Mode Explanation

According to the restrictions of strict mode (STRICT_TRANS_TABLES) in MySQL 5.0 and above:

1). Does not support inserting null values ​​into not null fields

2). It does not support inserting '' value into the auto-increment field, but null value can be inserted

3). Does not support default values ​​for text fields

Look at the following code: (the first field is an auto-increment field)

$query="insert into demo values('','$firstname','$lastname','$sex')";

The above code is only valid in non-strict mode.

Code

$query="insert into demo values(NULL,'$firstname','$lastname','$sex')";

The above code is only valid in strict mode. Replace the empty value '' with NULL.