MySQL 컬럼 자료형 확인

MySQL 컬럼 자료형 확인

1 방법 1: SHOW COLUMNS

mysql> SHOW COLUMNS FROM `user` LIKE 'user_id';
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| user_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+---------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

2 방법 2: information_schema

mysql> SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME='user' AND COLUMN_NAME='user_id';
+-----------+
| DATA_TYPE |
+-----------+
| int       |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME='user' AND COLUMN_NAME='user_id';
+------------------+
| COLUMN_TYPE      |
+------------------+
| int(10) unsigned |
+------------------+
1 row in set (0.00 sec)

3 방법 3: PHP

$mysqli = new mysqli('localhost','my_user','my_password','world');
$result = $mysqli->query("SHOW COLUMNS FROM `user` LIKE 'user_id'");
$row = mysqli_fetch_assoc($result);
$column_type = $row['Type'];

$mysqli = new mysqli('localhost','my_user','my_password','world');
$result = $mysqli->query("SELECT COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME='user' AND COLUMN_NAME='user_id'");
$row = mysqli_fetch_assoc($result);
$column_type = $row['COLUMN_TYPE'];
echo $column_type;

4 같이 보기

• MySQL 컬럼 있는지 확인

5 참고

• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eed0fc85-f1c2-452b-a882-64e2f031d070/how-to-get-datatype-a-column-in-sqlserver-