テーブル(tinyint, char,date )に以下のようなINSERT文を実行した場合どのような結果になるか?
INSERT INTO test_numbers VALUES (1,22,333);
検証してみました。

結果としては、22は文字列 ’22’ に変換してInsertされたがdate型は333をおかしな日付として認識
してしまい、0 date( 0000-00-00 )が格納されました。


mysql> CREATE TABLE test_numbers (number tinyint(3) UNSIGNED,string char(5),dates date);
Query OK, 0 rows affected (0.00 sec)

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> INSERT INTO test_numbers VALUES (1,22,333);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'dates' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
+--------+--------+------------+
1 row in set (0.00 sec)

mysql>

numbers

—————————————————————————————–
「検証」    tinyint unsignedへの255以上の数値,char(5)への5を超えた値,
date型への’1000-01-01’以前の日付のinsert結果。
—————————————————————————————–


mysql> INSERT INTO test_numbers VALUES (1000,'yahoo','999-01-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'number' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
+--------+--------+------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO typetest VALUES (1000,'yoodoo','999-12-31');
ERROR 1146 (42S02): Table 'STUDY.typetest' doesn't exist
mysql> INSERT INTO test_numbers VALUES (1000,'yoodoo','999-12-31');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'number' at row 1 |
| Warning | 1265 | Data truncated for column 'string' at row 1 |
+---------+------+-------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
| 255 | yoodo | 0999-12-31 |
+--------+--------+------------+
3 rows in set (0.00 sec)

mysql>

mysql> INSERT INTO test_numbers VALUES (255,'google','1000-01-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
| 255 | yoodo | 0999-12-31 |
| 255 | googl | 1000-01-01 |
+--------+--------+------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO test_numbers VALUES (255,'goo','1000-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
| 255 | yoodo | 0999-12-31 |
| 255 | googl | 1000-01-01 |
| 254 | googl | 1000-01-01 |
| 254 | googl | 1000-01-01 |
| 255 | goo | 1000-01-01 |
+--------+--------+------------+
7 rows in set (0.00 sec)

mysql>


mysql> CREATE TABLE test_numbers2 (number tinyint(3) UNSIGNED,string char(10),dates date);
Query OK, 0 rows affected (0.01 sec)

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

mysql> INSERT INTO test_numbers2 VALUES (254,'google.com','1000-01-01'),(255,'yahoo.com','1000- 12-31');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test_numbers2;
+--------+------------+------------+
| number | string | dates |
+--------+------------+------------+
| 254 | google.com | 1000-01-01 |
| 255 | yahoo.com | 1000-12-31 |
+--------+------------+------------+
2 rows in set (0.00 sec)

mysql>

char

Comments are closed.

Post Navigation