SHOW CREATE TABLE取出的语句，运行为何总是有语法错误呢?

RogerZhuo

技术背景：
刚开始学习MySQL时候，有时偷懒，会用SHOW CREATE TABLE 表名\G来复制表创建语句，可是当运行的时候总会因为"表名和列名上有单引号",提示语法错误不能运行。
问题列表：
1，为什么会出错呢？
2，有什么解决方法？
解决问题：
1，分析show create table拷贝的语句出错原因
1.1 重现过程
1.1.1 创建测试表test，并通过show create table test取得表的创建语句，可见表名，列名都用引号包着。
mysql> create table test(
-> id int not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test \G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
1.1.2 drop掉test表，再复制刚才的创建语句，执行后，出现预期的语法错误。
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
mysql> Create Table: CREATE TABLE `test` (
-> `id` int(11) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ': CREATE

1.2 原理
1.2.1 如果仔细看`test`与单引号的'test'外观上有点区别，那用字符串函ASCII()来验证一下(复制语句中`)。
从下面测试结果可知，`的ASCII码是96， 那查看码表后，才知`是"重音符"，不是ASCII为39的单引号。
mysql> select ASCII('`');
+------------+
| ASCII('`') |
+------------+
| 96 |
+------------+
//查看单引号的ASCII码
mysql> select ASCII("'");
+------------+
| ASCII("'") |
+------------+
| 39 |
+------------+
说明：重音符在键盘第二排第一个键，发现的时候，我表示相当尴尬。
2 解决问题
2.1 利用Session设置参数set sql_quote_show_create=0;
2.1.1 sql_quote_show_create，有两个值（1，0），默认是1，表示表名和列名会用``包着的。
这个服务器参数只可以在session级别设置，不支持global设置的(不支持my.cnf设置)。
设置后，可见下面的没有重音符了。
mysql> set sql_quote_show_create=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test \G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2.2 使用pager来处理输出
mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE test (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

3, 命令行的使用：偷懒是一回事，如果在写shell, python等工具的时候，可能会根据show create table来处理一些事情，即“命令行处理”
3.1 用'SET SQL_QUOTE_SHOW_CREATE=0
-bash-3.2$ mysql -uroot -e 'SET SQL_QUOTE_SHOW_CREATE=0; use test; show create table test';
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE test (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------+
3.2
-bash-3.2$ mysql -e 'use test; show create table test \G' | tr -d '`';
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3.3 也可以使用sed来解决
-bash-3.2$ mysql -e 'use test; show create table test \G' | sed -e 's/`//g';
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
参考：
MySQL 5.1 英文文档471页
http://datacharmer.blogspot.com/ ... here-is-no-way.html

小版主杀手

学习了

RogerZhuo

回复 2# 小版主杀手
，只是一个蛋疼的小问题，实用性不大。

   

devilkin0312

``着重号  mysql 会把这其中部分认为是一个整体值，避免歧义和关键词
‘’ 单引号，
另外，楼主你想多了，可以直接copy然后执行的，只是你复制多了
mysql> Create Table: CREATE TABLE `test` (
-> `id` int(11) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-> ;
“create table：”这2个词不用复制

devilkin0312

如果在程序里面，直接匹配到create table `test`(........就可以了

RogerZhuo

回复 5# devilkin0312
确实呢！！！谢谢指正，差点误人子弟了