PK以外の通常INDEXのレビュー

————————————————————————
インデックス作成時のインデックス名
————————————————————————

ALTER TABLE … ADD INDEX
インデックス名を指定しないで実行すると, MySQLがインデックスの最初のColumを利用して自動的に名前を付ける。

CREATE INDEX
インデックス名を指定しないで作成しようとするとエラーになる。


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> explain select * from test_numbers where string='yahoo';
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_numbers | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table test_numbers add index idx_tn_string(string);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0

add_index_0

インデックス追加後の実行プラン
add_index_1


mysql> drop index idx_tn_string on test_numbers;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create index idx_tn_string on test_numbers(string);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

add_index_2

alter tableにてインデックスのDrop


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test_numbers drop index idx_tn_string;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

補足: テーブル作成時にインデックスを追加する例


mysql> CREATE TABLE T_WITH_INDX (
-> col1 INT UNSIGNED NOT NULL,
-> col2 CHAR(50) NOT NULL,
-> col3 CHAR(50) NOT NULL,
-> PRIMARY KEY(col1),
-> UNIQUE(col2),
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

index_01

同じ内容で以下のようにインデックスを作成する事も可能


mysql> CREATE TABLE T_WITH_INDX(
-> col1 INT UNSIGNED NOT NULL PRIMARY KEY,
-> col2 CHAR(50) NOT NULL UNIQUE,
-> col3 CHAR(50) NOT NULL,
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

DROP INDEXではまとめて複数のインデックスをDROPする事は出来ません。
纏めて削除したい場合は、ALTER TABLE … DROP INDEXを利用する。


MS SQLなどだとSP_RENAMEなどのストアドプロシジャーを利用してテーブル名を変更していましたが、
MYSQLはやはり、オラクルのよりのDBなのでオラクルと同じように
”ALTER TABLE 既存テーブル名 RENAME TO 新規テーブル名;”で変更してあげるようです。
テーブル名の変更はあまり頻度の高い操作ではありませんが、データのフラグメンテーションを
解消するために別テーブルを作成しておいて、メンテナンスと同時に名前を変更して切り替える…………
年度毎にテーブル名を変更して、空の新しいテーブルに新年度からのデータを入れるなど……..
色々と便利ば利用方法もありますので、いざという時に直ぐに思い出せるようにメモしておきます。

——————————————————————
MYSQLでのテーブルのRENAMEの方法①
——————————————————————


mysql> use STUDY
Database changed
mysql> show tables;
+------------------+
| Tables_in_STUDY |
+------------------+
| T1 |
| TIMESTAMP_FUNC |
| Y2008 |
| Y2009 |
| Y2010 |
+------------------+
16 rows in set (0.13 sec)

mysql> desc T1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
1 row in set (0.06 sec)

mysql> ALTER TABLE T1 RENAME TO K1;
Query OK, 0 rows affected (0.04 sec)

mysql> desc K1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_STUDY |
+------------------+
| K1 |
| TIMESTAMP_FUNC |
| Y2008 |
| Y2009 |
| Y2010 |
+------------------+
16 rows in set (0.01 sec)

mysql>

alter_rename

——————————————————————
MYSQLでのテーブルのRENAMEの方法②
——————————————————————


mysql> desc STUDY.test_col;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| col1 | int(4) unsigned | YES | | NULL | |
| col2 | int(4) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> RENAME TABLE STUDY.test_col TO STUDY.RENAME_TEST;
Query OK, 0 rows affected (0.01 sec)

mysql> desc STUDY.RENAME_TEST;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| col1 | int(4) unsigned | YES | | NULL | |
| col2 | int(4) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

rename_table

※ MYISAM, INNODBともに*.frmファイルの名前はRENAMEと同時に自動で変わります。
尚、MYISAMのデータファイルなども同じくファイル名も自動で変わるようです。

RENAME_TEST.frm
RENAME_TEST.MYD
RENAME_TEST.MYI

————————————————
参考サイト
————————————————
12.1.19. RENAME TABLE 構文

このステートメントは1つ、または複数のテーブルのリネームをします。

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

リネームは自動的に行われますので、その他のスレッドはリネーム作業中はどのテーブルにも
アクセスできません。例えば、もし既存テーブル old_table があるとしたら、同じ構成で中身が
空の別のテーブル new_table を作成し、その後、次のように既存テーブルと空のテーブルを
入れ替える事ができます。(backup_table は存在していないと仮定する):


CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

もしこのステートメントが複数のテーブルをリネームすると、残りの作業は左から右に行われます。
もし2つのテーブル名の入れ替えをしたければ、このように行う事ができます。(tmp_table が存在しないと仮定):


RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;

2つのデータベースが同じファイル システム上にある限り、テーブルを1つのデータベースから
別のDBに移動するのに RENAME TABLE を利用する事ができます。

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

リネームされたテーブルやビューに与えられた権限は、新しい名前に移動しません。
それらは手動で変更しなければいけません。

※ 実証確認
権限テーブルは、テーブル名変更前も変更後も同じ名前で残っている事を確認。

mysql> select Host,User,DB,Table_name,Table_priv from tables_priv where User = 'restrict_user';
+------+---------------+-------+-------------+------------+
| Host | User | DB | Table_name | Table_priv |
+------+---------------+-------+-------------+------------+
| % | restrict_user | STUDY | RENAME_TEST | Select |
+------+---------------+-------+-------------+------------+
1 row in set (0.00 sec)

mysql> RENAME TABLE STUDY.RENAME_TEST TO TEST.RENAME_TEST;
Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User,DB,Table_name,Table_priv from tables_priv where User = 'restrict_user';
+------+---------------+-------+-------------+------------+
| Host | User | DB | Table_name | Table_priv |
+------+---------------+-------+-------------+------------+
| % | restrict_user | STUDY | RENAME_TEST | Select |
+------+---------------+-------+-------------+------------+
1 row in set (0.00 sec)

mysql>

rename_to_other_db

テーブルは別DBに移動していて、ファイル自体もフォルダーが移動している事を確認。
rename_test

RENAMEにて関わるその他の情報

複数テーブルQuery1つでまとめてリネーム


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A1 |
| T_SiteResponse |
| innodb_monitor |
+----------------+
3 rows in set (0.00 sec)

mysql> rename table A1 to A10,T_SiteResponse to T10;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A10 |
| T10 |
| innodb_monitor |
+----------------+
3 rows in set (0.00 sec)

rename

TEMPORARY TABLEをリネームするにはRENAME TABLE は使えない


mysql> CREATE TEMPORARY TABLE t1 (i INT);
ERROR 1050 (42S01): Table 't1' already exists
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE T1 (i INT);
Query OK, 0 rows affected (0.00 sec)

mysql> rename table T1 to T100;
ERROR 1017 (HY000): Can't find file: './test/T1.frm' (errno: 2)
mysql> ALTER TABLE T1 RENAME TO T100;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

alter_rename1


インデックスはDBのパフォーマンスチューニングには欠かせないものだが、その中でも中心的な
キーとなるPKを改めて確認してみました。

PRIMARY KEY は、すべてのキーカラムが NOT NULL として定義されていなければならないユニーク KEY である。
NOT NULL として明示的に定義されていないと、暗黙的(かつ自動的)に NOT NULL に設定される。
MySQL において、このキーは PRIMARY と呼ばれる。個々のテーブルは PRIMARY KEY を 1 つだけ持つことが出来る。
PRIMARY KEY がない場合に、何らかのアプリケーションがテーブルの PRIMARY KEY を要求すると、MySQL
では、NULL カラムをまったく持たない最初の UNIQUE キーが PRIMARY KEYとして返される。

複合インデックスを PRIMARY KEY にすることもできる。しかし、カラムの仕様で PRIMARY KEY キー属性
を使用して複合インデックスは作成することはできない。そのようにしても、単一のカラムがプライマリとして
マークされるにすぎない。 この場合、別に PRIMARY KEY(index_col_name, …) 節を使用する必要がある。

    インデックス確認方法

mysql> show index from テーブル名\G
mysql> desc テーブル名;

show_index

PKの追加


mysql> CREATE TABLE `Y2008` (
-> `comment` varchar(50) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

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

mysql> alter table Y2008 add
-> constraint PK_Y2008 primary key (comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

pk

PKの削除


mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> drop index `PRIMARY` on Y2008;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

drop_index1


mysql> alter table Y2008 add constraint PK_Y2008 primary key (comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table Y2008 drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

drop_index2

——————————————————
テーブルに付与されたインデックス確認コマンド
——————————————————
mysql> show create table テーブル名;
mysql> show index from テーブル名;

table_show

——————————————————
テーブル作成時に追加する例
——————————————————

其の①
CREATE TABLE TABLE_ID(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(10)
);

其の②

CREATE TABLE TBL1 (col1 INT NOT NULL PRIMARY KEY);

其の③

CREATE TABLE TBL2 (
col1 INT NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY (col1, col2)
);

index3


TIMESTAMP列はDEFAULTでは、NOT NULLに設定されます。
あらかじめNULL値が入る事を想定している場合は明示的にNULLを
指定してテーブルを作成する。

以下テーブル作成後のALTER TABLEにて属性変更している。


mysql> CREATE TABLE timestamp_null (
-> data_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> i INT
-> );
Query OK, 0 rows affected (0.16 sec)

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> ALTER TABLE timestamp_null
-> MODIFY data_time TIMESTAMP NULL
-> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>


mysql> INSERT INTO timestamp_null (data_time, i) VALUES (NULL, 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+-----------+------+
| data_time | i |
+-----------+------+
| NULL | 10 |
+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_null (data_time, i) VALUES (now(), 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+---------------------+------+
| data_time | i |
+---------------------+------+
| NULL | 10 |
| 2009-07-15 04:31:42 | 10 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql>

null_timestamp

TIMESTAMP関連の検証


mysql> CREATE TABLE timestamp_chk (data_time TIMESTAMP NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> desc timestamp_chk;
+-----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| data_time | timestamp | YES | | NULL | |
+-----------+-----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO timestamp_chk VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+-----------+
| data_time |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES ('文字列');
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 | ← 文字列は対象外なので"0" Valueがinsertされる。
+---------------------+
2 rows in set (0.00 sec)

mysql>

timestamp_check

日付列に12 Digit と14 DigitのNumberをInsertした時の違い


mysql> INSERT INTO timestamp_chk VALUES (200202082139);
Query OK, 1 row affected (0.01 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES (20020208213900);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
+---------------------+
4 rows in set (0.00 sec)

timestamp_digit

無効な日付をINSERTした場合

    ※2月31日は存在しない日付

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-31 23:59:59');
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

    ※60秒が指定されている。

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-28 23:59:60');
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql>

timestamp_digit_2


テーブルに対してALTER TABLE文でデータ型を変更したりする場合、
必ずバックアップを取得しておかないと後戻り出来ない場合があります。
作業の際は細心の注意をして対応しましょう。
もしくは、作業の際は以下のようなSQLモードをONにして対応すると安全かもしれません。

——————————————————————-
STRICT_TRANS_TABLES
STRICT_ALL_TABLES
——————————————————————-

SQLモードの確認は以下のようなQUERYにて確認出来ます。

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

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

mysql>

alter_table5


mysql> CREATE TABLE `T_ALTER` (
-> `number` int NULL DEFAULT NULL,
-> `comment` varchar(100) NULL DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> desc T_ALTER;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into T_ALTER values(1000000,'This is test table de su');
Query OK, 1 row affected (0.00 sec)

mysql> insert into T_ALTER values(NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql>

alter_table

データ型を変更するとどのような影響があるか。。。。


mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE T_ALTER
-> MODIFY number TINYINT UNSIGNED NOT NULL,
-> MODIFY comment TINYINT UNSIGNED NOT NULL;
Query OK, 2 rows affected, 4 warnings (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 3

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'number' at row 1 |
| Warning | 1366 | Incorrect integer value: 'This is test table de su' for column 'comment' at row 1 |
| Warning | 1265 | Data truncated for column 'number' at row 2 |
| Warning | 1265 | Data truncated for column 'comment' at row 2 |
+---------+------+-----------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from T_ALTER;
+--------+---------+
| number | comment |
+--------+---------+
| 255 | 0 | <----- TINYINT UNSIGNEDのMAX値に置き換えられてしまった。 | 0 | 0 | +--------+---------+ 2 rows in set (0.00 sec) mysql>

alter_table2

再度テーブルの定義を直して見てもデータは戻らない。。。。
本番環境のデータベースであればリストアが必要だ。。。


mysql> select * from T_ALTER;
+--------+---------+
| number | comment |
+--------+---------+
| 255 | 0 |
| 0 | 0 |
+--------+---------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE T_ALTER
-> MODIFY number int NULL DEFAULT NULL,
-> MODIFY comment varchar(100) NULL DEFAULT NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from T_ALTER;
+--------+---------+
| number | comment |
+--------+---------+
| 255 | 0 |
| 0 | 0 |
+--------+---------+
2 rows in set (0.00 sec)

mysql>

alter_table3

失敗したくないときは、自分の作業セッションでStrict Modeを有効にすると良いかもしれません。
4.2.6. SQL モード


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> CREATE TABLE `T_ALTER` (
-> `number` int NULL DEFAULT NULL,
-> `comment` varchar(100) NULL DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T_ALTER values(1000000,'This is test table de su');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T_ALTER values(NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE T_ALTER
-> MODIFY number TINYINT UNSIGNED NOT NULL,
-> MODIFY comment TINYINT UNSIGNED NOT NULL;
ERROR 1264 (22003): Out of range value for column 'number' at row 1
mysql> select * from T_ALTER;
+---------+--------------------------+
| number | comment |
+---------+--------------------------+
| 1000000 | This is test table de su |
| NULL | NULL |
+---------+--------------------------+
2 rows in set (0.00 sec)

mysql>

alter_table4