sql_mode with MariaDB Xpand

Overview

MariaDB Xpand provides the sql_mode system variable to enable or disable SQL modes that impact database behavior:

  • Xpand allows the SQL mode to be configured globally or for a single session

  • Xpand provides SQL modes that impact data type validation and generation of AUTO_INCREMENT values

  • For compatibility, Xpand accepts and discards SQL modes supported by MariaDB Enterprise Server and MySQL

For a full list of SQL modes supported by Xpand, see "SQL Modes for MariaDB Xpand".

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Set the SQL Mode

Xpand allows the SQL mode to be configured globally or for a single session.

The SQL mode can be configured by setting the sql_mode system variable using the SET statement. The sql_mode system variable can be set to a comma-separated list of SQL modes.

Set the Global SQL Mode

To set the global SQL mode, set the sql_mode system variable using the SET GLOBAL statement:

SET GLOBAL sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES';

To disable all SQL modes globally, set the sql_mode system variable to the empty string:

SET GLOBAL sql_mode = '';

When the global SQL mode is changed, only new sessions are affected.

Set the Session SQL Mode

To set the SQL mode for the current session, set the sql_mode system variable using the SET SESSION statement:

SET SESSION sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES';

To disable all SQL modes for the current session, set the sql_mode system variable to the empty string:

SET SESSION sql_mode = '';

Show the SQL Mode

The SQL mode can be retrieved by showing the value of the sql_mode system variable using the SHOW VARIABLES statement.

Show the Global SQL Mode

To show the global SQL mode, show the sql_mode system variable using the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES LIKE 'sql_mode';

+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+

Alternatively, the global value of the sql_mode system variable can be queried using the SELECT statement:

SELECT @@global.sql_mode;

+---------------------+
| @@global.sql_mode   |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+

Show the Session SQL Mode

To show the SQL mode for the current session, show the sql_mode system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES LIKE 'sql_mode';

+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+

Alternatively, the session value of the sql_mode system variable can be queried using the SELECT statement:

SELECT @@session.sql_mode;

+---------------------+
| @@session.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+