About 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 system variables The value of sql_mode. This article mainly introduces the viewing and setting of this value, mainly under Mac system.

The meaning and function of each mode can be easily found online and will not be introduced in this article.

It can be divided into 3 levels according to the area of ​​effect and time, namely session level, global level, and configuration (permanent) level.

Session level:

View-

select @@session.sql_mode;

Modify-

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

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

Global level:

View-

select @@global.sql_mode;

Modify-

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

Requires advanced permissions. It will only take effect for the next connection. It will not affect the current session (tested by myself). It will be invalid after MySQL restarts because MySQL will re-read the corresponding value in the configuration file when it restarts. If it needs to be permanent, it needs to be modified. value in the configuration file.

Configuration modification (permanent):

Open vi /etc/my.cnf

Add

[mysqld]
sql-mode = "xx_mode"

Note below :[mysqld] must be added, and the middle of sql-mode is "-" instead of an underscore.

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

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

ps

The last thing to add is the start, stop, restart and other operations of MySQL under Mac.

There are two main ways.

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

The second is the command line method.

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

/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 environment variables, which can simplify writing. How to add them will not be introduced.

Recommendedmysql video tutorial, address: //m.sbmmt.com/course/list/51.html

The above is the detailed content of About viewing and setting SQL Mode in MySQL. For more information, please follow other related articles on the PHP Chinese website!