Description:
SHOW CREATE statements are inconsistent in the
output they produce with respect to the ANSI_QUOTES
setting.
With ANSI_QUOTES disabled, SHOW CREATE {TABLE,
DATABASE, and FUNCTION|PROCEDURE} all quote
names using backticks:
mysql> SHOW CREATE TABLE t;
+-------
+----------------------------------------------------------------------------------
----+
| Table | Create Table
|
+-------
+----------------------------------------------------------------------------------
----+
| t | CREATE TABLE `t` (
`i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------
+----------------------------------------------------------------------------------
----+
1 row in set (0.01 sec)
mysql> SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE FUNCTION f;
+----------+--------------------------------------------+
| Function | Create Function |
+----------+--------------------------------------------+
| f | CREATE FUNCTION `f`() RETURNS int
return 1 |
+----------+--------------------------------------------+
1 row in set (0.00 sec)
With ANSI_QUOTES enabled, SHOW CREATE TABLE quotes
names with double quotes, but SHOW CREATE DATABASE
and SHOW CREATE FUNCTION|PROCEDURE continue to use
backticks:
mysql> SET @@sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE t;
+-------
+----------------------------------------------------------------------------------
----+
| Table | Create Table
|
+-------
+----------------------------------------------------------------------------------
----+
| t | CREATE TABLE "t" (
"i" int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------
+----------------------------------------------------------------------------------
----+
1 row in set (0.00 sec)
mysql> SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE FUNCTION f;
+----------+--------------------------------------------+
| Function | Create Function |
+----------+--------------------------------------------+
| f | CREATE FUNCTION `f`() RETURNS int
return 1 |
+----------+--------------------------------------------+
1 row in set (0.00 sec)
How to repeat:
See above.
Description: SHOW CREATE statements are inconsistent in the output they produce with respect to the ANSI_QUOTES setting. With ANSI_QUOTES disabled, SHOW CREATE {TABLE, DATABASE, and FUNCTION|PROCEDURE} all quote names using backticks: mysql> SHOW CREATE TABLE t; +------- +---------------------------------------------------------------------------------- ----+ | Table | Create Table | +------- +---------------------------------------------------------------------------------- ----+ | t | CREATE TABLE `t` ( `i` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +---------------------------------------------------------------------------------- ----+ 1 row in set (0.01 sec) mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE FUNCTION f; +----------+--------------------------------------------+ | Function | Create Function | +----------+--------------------------------------------+ | f | CREATE FUNCTION `f`() RETURNS int return 1 | +----------+--------------------------------------------+ 1 row in set (0.00 sec) With ANSI_QUOTES enabled, SHOW CREATE TABLE quotes names with double quotes, but SHOW CREATE DATABASE and SHOW CREATE FUNCTION|PROCEDURE continue to use backticks: mysql> SET @@sql_mode='ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE t; +------- +---------------------------------------------------------------------------------- ----+ | Table | Create Table | +------- +---------------------------------------------------------------------------------- ----+ | t | CREATE TABLE "t" ( "i" int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +---------------------------------------------------------------------------------- ----+ 1 row in set (0.00 sec) mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE FUNCTION f; +----------+--------------------------------------------+ | Function | Create Function | +----------+--------------------------------------------+ | f | CREATE FUNCTION `f`() RETURNS int return 1 | +----------+--------------------------------------------+ 1 row in set (0.00 sec) How to repeat: See above.