sql_mode 之 ignore_space

table t(id int not null primary key auto_increment, x int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t(x) values(1),(2),(3),(4),(5),(6); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select count(*) from t; -- 查看t表中有多少行数据 +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)

　　上面这个例子还是比较“中规中矩”、但是生活中又总是有一些人“放荡不羁有自由”；比如他们要建一张表、表名就叫count!

 

2、建立一张名叫count的表(1)

<span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> <span style="color: #ff00ff">count</span>(x <span style="color: #0000ff">int</span><span style="color: #000000">);
ERROR </span><span style="color: #800000; font-weight: bold">1064</span> (<span style="color: #800000; font-weight: bold">42000</span>): You have an error <span style="color: #808080">in</span> your SQL syntax; <span style="color: #0000ff">check</span> the manual that corresponds <span style="color: #0000ff">to</span> your MySQL server version <span style="color: #0000ff">for</span> the <span style="color: #808080">right</span> syntax <span style="color: #0000ff">to</span> <span style="color: #0000ff">use</span> near <span style="color: #ff0000">‘</span><span style="color: #ff0000">count(x int)</span><span style="color: #ff0000">‘</span> at line <span style="color: #800000; font-weight: bold">1</span>

　　看到这个语法错误你可能会想到count是关键字、是不是要明确的标记出来呢？ 于是

 

3、建立一张名叫count的表(2)

<span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> `<span style="color: #ff00ff">count</span>`(x <span style="color: #0000ff">int</span><span style="color: #000000">);
Query OK, </span><span style="color: #800000; font-weight: bold">0</span> rows affected (<span style="color: #800000; font-weight: bold">0.02</span> sec)

　　惊不惊喜？ 我想答案一定的否定的、因为我们今天主讲的是ignore_space这个sql_mode 然而它到这里了还没有登场！ 就已经有了一种快完了的感觉。

 

4、建立一张名叫count的表(3)

<span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> <span style="color: #ff00ff">count</span> (x <span style="color: #0000ff">int</span><span style="color: #000000">);
Query OK, </span><span style="color: #800000; font-weight: bold">0</span> rows affected (<span style="color: #800000; font-weight: bold">0.02</span> sec)

 

5、而2，3，4我们知道表有两种建法、一是加反引号的 二是加空格的；但是对于函数count的调用也可以有两种写法吗？

<span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span> (<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span><span style="color: #000000"> t;
ERROR </span><span style="color: #800000; font-weight: bold">1064</span> (<span style="color: #800000; font-weight: bold">42000</span>): You have an error <span style="color: #808080">in</span> your SQL syntax; <span style="color: #0000ff">check</span> the manual that corresponds <span style="color: #0000ff">to</span> your MySQL server version <span style="color: #0000ff">for</span> the <span style="color: #808080">right</span> syntax <span style="color: #0000ff">to</span> <span style="color: #0000ff">use</span> near <span style="color: #ff0000">‘</span><span style="color: #ff0000">*) from t</span><span style="color: #ff0000">‘</span> at line <span style="color: #800000; font-weight: bold">1</span>

　　由这里可以看到函数与括号之间默认是不能有空格的、如果有的话会报错；有没有一种方式可以让mysql兼容函数的两种写法呢？

　　有它就是我们今天的主角igonre_space 这个SQL_MODE

 

6、通过sql_mode来兼容两种count函数的写法

<span style="color: #0000ff">select</span> <span style="color: #008000; font-weight: bold">@@sql_mode</span><span style="color: #000000">;
</span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------------------------------------------+</span>
<span style="color: #808080">|</span> <span style="color: #008000; font-weight: bold">@@sql_mode</span>                                              <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------------------------------------------+</span>
<span style="color: #808080">|</span> STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------------------------------------------+</span>
<span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">set</span> <span style="color: #008000; font-weight: bold">@@session</span>.sql_mode<span style="color: #808080">=</span>concat(<span style="color: #008000; font-weight: bold">@@sql_mode</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">,IGNORE_SPACE</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
Query OK, </span><span style="color: #800000; font-weight: bold">0</span> rows affected, <span style="color: #800000; font-weight: bold">1</span> warning (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> <span style="color: #008000; font-weight: bold">@@sql_mode</span><span style="color: #000000">;
</span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------------------------------------------------------------------+</span>
<span style="color: #808080">|</span> <span style="color: #008000; font-weight: bold">@@sql_mode</span>                                                           <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------------------------------------------------------------------+</span>
<span style="color: #808080">|</span> IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------------------------------------------------------------------+</span>
<span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span> (<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span><span style="color: #000000"> t;
</span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+</span>
<span style="color: #808080">|</span> <span style="color: #ff00ff">count</span> (<span style="color: #808080">*</span>) <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+</span>
<span style="color: #808080">|</span>         <span style="color: #800000; font-weight: bold">6</span> <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+</span>
<span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span><span style="color: #000000"> t;
</span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
<span style="color: #808080">|</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
<span style="color: #808080">|</span>        <span style="color: #800000; font-weight: bold">6</span> <span style="color: #808080">|</span>
<span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
<span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

sql_mode加上ignore_space 有什么不足的地方？

　　1、由于它直接忽略了空格、所以就造成了有的语法就不起作用了、如：create table count (x int);

　　2、出现这种需求时多半是不好的编程习惯引起的、如刚才的例子当中、居然给表起了一个叫“count”的名字。 

 

 

 

----

sql_mode 之 ignore_space

标签：今天   key   prim   span   div   row   class   mode   错误