How can I see the specific value of the sql_mode?

Question

There are some sql_mode values in MySQL:

ANSI,

IGNORE_SPACE,

STRICT_TRANS_TABLES, etc

How can I see the one particular value? The manual says:

You can retrieve the current mode by issuing a SELECT @@sql_mode statement.

But it just shows nothing, just one blank field in a table with @@sql_mode as a column name.

Logarithm

Answer 1

It's only blank for you because you have not set the sql_mode. If you set it, then that query will show you the details:

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> set sql_mode=ORACLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Humbuggery

Thank you, this works. But some of them have their default values. I meant I wanted to see the defaults without setting the value. Or it's not possible? – Logarithm

The combination modes are all documented here: dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html. For example, here you can see which 7 modes comprise the ORACLE combination mode: dev.mysql.com/doc/refman/5.5/en/… – Humbuggery

"The default SQL mode is empty (no modes set)." Quoted from the MySQL Server Administration manual page for Server SQL Modes. I interpret that to mean that none of the special sql_mode's are set out of the box. – Dicephalous

@IkeWalker, Do you mean to say that by default (the "blank" mode), it is equivalent to as though all of PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER and etc are not set? Or do you mean that some of them are set but simply not shown by default? – Ashram

@Ashram the former is correct. If @@sql_mode is empty (the "blank" mode as you call it), then no sql_mode is set. I won't comment on the default sql_mode because that depends which version of MySQL you are running. – Humbuggery

Answer 2

You can also try this to determine the current global sql_mode value:

SELECT @@GLOBAL.sql_mode;

or session sql_mode value:

SELECT @@SESSION.sql_mode;

I also had the feeling that the SQL mode was indeed empty.

Counterinsurgency

official doc: dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting – Paramilitary

Answer 3

You need to login to your mysql terminal first using mysql -u username -p password

Then use this:

SELECT @@sql_mode; or SELECT @@GLOBAL.sql_mode;

output will be like this:

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUB

You can also set sql mode by this:

SET GLOBAL sql_mode=TRADITIONAL;

Cletus