MySQLのデータ型確認はshow columnsで。データ型変更時には、外部キーに注意

MySQLのデータ型についてまとめています。

以下、MySQL8.0.19を前提に解説しています。また、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。

MySQLのデータ型の一覧

MySQLには、数値型、日付・時間型、文字列型のデータ型があります。

参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型

数値型

• 整数型 (真数値)  INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT
• 固定小数点型 (真数値)  DECIMAL、NUMERIC
• 浮動小数点型 (概数値)  FLOAT、DOUBLE
• ビット値型  BIT

日付・時間型の一覧

• DATE、DATETIME、および TIMESTAMP 型
• TIME 型
• YEAR 型

文字列型の一覧

• CHAR および VARCHAR 型
• BINARY および VARBINARY 型
• BLOB 型と TEXT 型
• ENUM 型
• SET 型

テーブルカラムのデータ型確認はshow columns

カラムのデータ型を確認するshow columnsの構文は以下の通りです。

show columns from テーブル名

employees（社員テーブル）のカラムのデータ型を確認してみましょう。

mysql> show columns from employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

like を使って、ワイルドカードによるテーブル指定も可能です。長いテーブル名を間違えずに打ち込むのが大変なときなどに、役立ちます。

mysql> show columns from employees like 'emp%';
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| emp_no | int  | NO   | PRI | NULL    |       |
+--------+------+------+-----+---------+-------+

desc テーブル名でも、全く同様の出力になります。短くてタイプが楽ですが、likeによるワイルドカード指定はできません。

また、mysqlshowコマンドでも、カラムのデータ型の確認が可能です。

【関連記事】
▶MySQLのデータベース一覧確認は、show databasesよりmysqlshowが便利

データ型の変更は、alter table

データ型の変更をおこなうSQLの構文は以下の通りです。

ALTER TABLE テーブル名 MODIFY カラム名 データ型

変更前のテーブルemployees（社員テーブル）の各カラムのデータ型は以下の通りです。

mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

last_name（姓名の姓）のデータ型をvarchar(16)から、vachar(50)に変更してみましょう。

NOT NULL制約が設定されているカラムに対してデータ型の変更を行うと、NOT NULL制約が解除されます。

mysql> alter table employees modify last_name varchar(50);
Query OK, 0 rows affected (2.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

last_nameの「Null」の項目がNOからYESに変化しているのが確認できます。

以下のように指定すると、NOT NULL制約を設定したままデータ型を変更可能です。

mysql> alter table employees modify last_name varchar(50) not null;
Query OK, 0 rows affected (2.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

なお、外部キー（foreign key）として設定されているカラムは、データ型の変更ができません。いったん外部キーを解除してから、変更を行いましょう。

mysql> alter table employees modify emp_no varchar(10);
ERROR 3780 (HY000): Referencing column 'emp_no' and referenced column 'emp_no' in foreign key constraint 'dept_emp_ibfk_1' are incompatible.

まとめ

ポテパンダの一言メモ

• MySQLのデータ型は、大別すると数値型、日付・時刻型、文字列型の3種類。
• データ型を確認するには、show columns（SQL）や、mysqlshowコマンドを使う
• データ型変更は、alter tableで可能