Skip to content

SQL Mode

sql_mode is a system parameter in MatrixOne that specifies the mode of query and operation in MatrixOne. sql_mode can affect MatrixOne's syntax and semantic rules, thereby changing the behavior of MatrixOne querying SQL. In this article, you will introduce the mode, function of sql_mode and how to set SQL mode.

Note

MatrixOne currently only supports ONLY_FULL_GROUP_BY mode, and other modes are only supported for syntax. ONLY_FULL_GROUP_BY is used to control the behavior of the GROUP BY statement. When ONLY_FULL_GROUP_BY mode is enabled, MatrixOne requires that the columns in the GROUP BY clause in the SELECT statement must be an aggregate function (such as SUM, COUNT, etc.) or a column that appears in the GROUP BY clause. If there are columns in the SELECT statement that do not meet this requirement, an error will be thrown. If your table structure is complex, for the convenience of querying, you can choose to turn off the ONLY_FULL_GROUP_BY mode.

View sql_mode

Use the following command in MatrixOne to view sql_mode:

SELECT @@global.sql_mode;--Global Mode
SELECT @@session.sql_mode;--Session Mode

Set sql_mode

Set sql_mode in MatrixOne using the following command:

set global sql_mode = 'xxx' --Global mode, reconnection to the database takes effect
set session sql_mode = 'xxx'--session mode

Example

CREATE TABLE student(
id int,
name char(20),
age int,
nation char(20)
);

INSERT INTO student values(1,'tom',18,'Shanghai'),(2,'jan',19,'Shanghai'),(3,'jen',20,'Beijing'),(4,'bob',20,'Beijing'),(5,'tim',20,'Guangzhou');

mysql> select * from student group by nation;--This operation is not supported in `ONLY_FULL_GROUP_BY` mode
ERROR 1149 (HY000): SQL syntax error: column "student.id" must appear in the GROUP BY clause or be used in an aggregate function

mysql> SET session sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TAB
LES';--Close the ONLY_FULL_GROUP_BY mode of the current session
Query OK, 0 rows affected (0.02 sec)

mysql> select * from student group by nation;--Close `ONLY_FULL_GROUP_BY` mode takes effect immediately in the current session
+------+------+-------+
| id | name | age | nation |
+------+------+-------+
| 1 | tom | 18 | Shanghai |
| 3 | jen | 20 | Beijing |
| 5 | tim | 20 | Guangzhou |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> SET global sql_mode='ONLY_FULL_GROUP_BY';--Set global ONLY_FULL_GROUP_BY mode
Query OK, 0 rows affected (0.02 sec)

mysql> select * from student group by nation;--ONLY_FULL_GROUP_BY mode does not take effect, because the database needs to be reconnected after the global mode is turned on before it can take effect
+------+------+-------+
| id | name | age | nation |
+------+------+-------+
| 1 | tom | 18 | Shanghai |
| 3 | jen | 20 | Beijing |
| 5 | tim | 20 | Guangzhou |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> exit --Exit the current session

mysql> select * from student group by nation;--Reconnect the database and execute the query operation, ONLY_FULL_GROUP_BY mode is successfully turned on
ERROR 1149 (HY000): SQL syntax error: column "student.id" must appear in the GROUP BY clause or be used in an aggregate function