IGNORE_SPACEを有効にしているとき、パーサはファンクション名と後続の括弧間に余白が存在
してはいけないという要求を緩和します。このことで、ファンクション呼び出しの記述がより自由に行える
ようになります。例えば、次のどちらのファンクション呼び出しも有効です。

SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;


mysql> SELECT NOW ();
ERROR 1305 (42000): FUNCTION NOW does not exist
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2009-07-25 00:03:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW ();
ERROR 1305 (42000): FUNCTION NOW does not exist
mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW ();
+---------------------+
| NOW () |
+---------------------+
| 2009-07-25 00:04:13 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2009-07-25 00:04:19 |
+---------------------+
1 row in set (0.00 sec)

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

mysql>

sql_mode_space

参考サイト
—————————————————————-

8.2.4. 構文解析と解像度のファンクション名

4.2.6. SQL モード


SQL_MODEを変更するとMYSQLの動作は大きく変わってくる。

ANSI_QUOTES

引用文字 ‘`’ のように、‘”’ を識別子として扱う。文字列の引用文字ではない。このモードを有効にした場合には、
‘`’ を識別子として使用できる。ANSI_QUOTES を有効にした場合には、識別子として解釈されるため、
リテラル文字列の引用には二重引用符を使用できなくなる。


mysql> select comment from string_test limit 1;
+------------+
| comment |
+------------+
| THIS IS LO |
+------------+
1 row in set (0.00 sec)

mysql> select @@SQL_MODE;
+---------------------+
| @@SQL_MODE |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)

mysql> select "comment" from string_test limit 1;
+---------+
| comment |
+---------+
| comment |
+---------+
1 row in set (0.00 sec)

mysql> set SQL_MODE ='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_MODE;
+-------------+
| @@SQL_MODE |
+-------------+
| ANSI_QUOTES |
+-------------+
1 row in set (0.00 sec)

mysql> select "comment" from string_test limit 1;
+------------+
| comment |
+------------+
| THIS IS LO |
+------------+
1 row in set (0.00 sec)

mysql>

sql_mode_ansi

————————————————————————-

mysql> SELECT "Let's see whether this works.";
+-------------------------------+
| Let's see whether this works. |
+-------------------------------+
| Let's see whether this works. |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "Let's see whether this works.";
ERROR 1054 (42S22): Unknown column 'Let's see whether this works.
mysql> SELECT `Let's see whether this works.`;
ERROR 1054 (42S22): Unknown column 'Let's see whether this works.
mysql> SELECT 'Lets see whether this works.';
+------------------------------+
| Lets see whether this works. |
+------------------------------+
| Lets see whether this works. |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Let\'s see whether this works.';
+-------------------------------+
| Let's see whether this works. |
+-------------------------------+
| Let's see whether this works. |
+-------------------------------+
1 row in set (0.00 sec)

sql_mode
————————————————————————-


4.2.6. SQL モード


複数行をINSERTした時に列の長さを超えたデータと正常なデータをINSERTした
場合の結果は、SQL_MODEによって違う。

    以下の条件によっても結果は違う事に注意

MyISAM
InnoDB
STRICT_ALL_TABLES
STRICT_TRANS_TABLES


mysql> SELECT @@session.sql_mode;
+---------------------+
| @@session.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.01 sec)


mysql> CREATE TABLE string_test (
-> comment varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc string_test;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'comment' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

string_test


mysql> SET @@session.sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

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

mysql>


mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
ERROR 1406 (22001): Data too long for column 'comment' at row 1
mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

strict


DATE型に以下のように、2文字だけしか年を指定しないでINSERTした場合
MYSQLは自動的に

    1970-01-01 ~ 2069-12-31

の間の日付に変換する。
4桁で年を指定すればそのままINSERTされます。

以下検証


mysql> CREATE TABLE `T3` (
-> `BIRTH` DATE NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)

mysql> desc T3;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| BIRTH | date | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO T3 VALUES ('10-02-08');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T3 VALUES ('69-12-31');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO T3 VALUES ('70-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO T3 VALUES ('1910-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T3 VALUES ('2070-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
| 1910-01-01 |
| 2070-01-01 |
+------------+
5 rows in set (0.00 sec)

mysql>

date

======= SQLのモードによっても結果が異なる事があります。 ========

mysql> INSERT INTO T3 VALUES ('12:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
| 1910-01-01 |
| 2070-01-01 |
| 2012-00-00 |
+------------+
6 rows in set (0.01 sec)

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

mysql> INSERT INTO T3 VALUES ('12:00:00');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'BIRTH' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
| 1910-01-01 |
| 2070-01-01 |
| 2012-00-00 |
| 0000-00-00 |
+------------+
7 rows in set (0.00 sec)

mysql> set sql_mode ='';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO T3 VALUES ('12:00:00');
Query OK, 1 row affected (0.02 sec)

mysql>


MYSQLインストール直後のアカウント初期設定

基本的ですが、MYSQLサービスはrootでは動かさない。
MYSQLサーバー用 専用アカウントの作成

[mysql@colinux local]$ /etc/init.d/mysql.server start
Starting MySQL… SUCCESS!
[mysql@colinux local]$ ps -ef | grep mysql
root 2649 2623 0 12:17 tty1 00:00:00 su - mysql
mysql 2658 2649 0 12:17 tty1 00:00:00 -bash
mysql 2693 1 0 12:18 tty1 00:00:00 /bin/sh ./bin/mysqld_safe
--datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/colinux.pid
mysql 2821 2693 9 12:18 tty1 00:00:02 /usr/local/mysql/bin/mysqld
--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
--log-error=/usr/local/mysql/data/colinux.err
--pid-file=/usr/local/mysql/data/colinux.pid
--socket=/tmp/mysql.sock --port=3306
mysql 2836 2658 0 12:18 tty1 00:00:00 ps -ef
mysql 2837 2658 0 12:18 tty1 00:00:00 grep mysql

[mysql@colinux local]$

—————– my.cnf ———————
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 16K

mysqlインストール初期状態でまずはパスワードを設定して不要なアカウントを削除

[mysql@colinux local]$ mysql -u root mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> update user set password = PASSWORD('password2009')
-> where user ='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> delete from user where user = 'root' and host ='%';
Query OK, 0 rows affected (0.01 sec)

mysql> delete from user where user ='';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from db where user ='';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

init_secure

パスワードの付いていないアカウントがないかダブルチェック

mysql> select user,host,password from user
-> where user = '' or password = '';
Empty set (0.00 sec)

mysql>

今後の為に、パスワードの付いてないアカウントが作成されないように制限する


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

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

mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)

mysql>

no_auto_create_user

[mysql@colinux local]$ mysql -u root mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.00 sec)

mysql> GRANT select,insert,update,delete on DB001.* to 'no_password_user';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>

上記の”NO_AUTO_CREATE_USER”モードはMYSQLサーバーを再起動したら消えます。

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.01 sec)

mysql>

そこでmy.cnfに”sql_mode”の設定を行い再起動しても設定が消えないようにします。

—————————————————————–
抜粋
—————————————————————–
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
default-character-set=utf8
sql_mode =”NO_AUTO_CREATE_USER”
—————————————————————–

上記の設定を行い、mysqlを再起動すればパスワードの無いユーザーは
作成出来なくなります。後はルールとして周知すればOK。

sql_mode