Description:
SHOW CREATE DATABASE and SHOW CREATE TABLE
use ` to quote identifiers by default, and " if
ANSI_QUOTES is enabled. SHOW GRANTS, on the
other hand, ignores the setting of ANSI_QUOTES.
This is illustrated by the following session listing:
How to repeat:
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 table t;
+-------
+--------------------------------------------------------------------------------------
------------------+
| Table | Create Table |
+-------
+--------------------------------------------------------------------------------------
------------------+
| t | CREATE TABLE `t` (
`c` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |
+-------
+--------------------------------------------------------------------------------------
------------------+
1 row in set (0.01 sec)
mysql> show grants for cbuser@localhost;
+--------------------------------------------------------------------------------------
-------------------------+
| Grants for cbuser@localhost |
+--------------------------------------------------------------------------------------
-------------------------+
| GRANT USAGE ON *.* TO 'cbuser'@'localhost' IDENTIFIED BY PASSWORD
'*126CFB940B0843713B19A6C21B99C0F1F9F3AFB6' |
| GRANT ALL PRIVILEGES ON `cookbook`.* TO 'cbuser'@'localhost' |
+--------------------------------------------------------------------------------------
-------------------------+
2 rows in set (0.00 sec)
mysql> set sql_mode=ansi_quotes;
Query OK, 0 rows affected (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 table t;
+-------
+--------------------------------------------------------------------------------------
------------------+
| Table | Create Table |
+-------
+--------------------------------------------------------------------------------------
------------------+
| t | CREATE TABLE "t" (
"c" char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |
+-------
+--------------------------------------------------------------------------------------
------------------+
1 row in set (0.00 sec)
mysql> show grants for cbuser@localhost;
+--------------------------------------------------------------------------------------
-------------------------+
| Grants for cbuser@localhost |
+--------------------------------------------------------------------------------------
-------------------------+
| GRANT USAGE ON *.* TO 'cbuser'@'localhost' IDENTIFIED BY PASSWORD
'*126CFB940B0843713B19A6C21B99C0F1F9F3AFB6' |
| GRANT ALL PRIVILEGES ON `cookbook`.* TO 'cbuser'@'localhost' |
+--------------------------------------------------------------------------------------
-------------------------+
2 rows in set (0.00 sec)
Note that the database name in the second GRANT statement
is not quoted as "cookbook".
