MySQL 컬럼 있는지 확인

MySQL에서 컬럼 있는지 확인하기

1 방법 1: SHOW COLUMNS[ | ]

SHOW COLUMNS FROM `테이블명` LIKE '컬럼명';

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)

mysql> SHOW COLUMNS FROM `user` LIKE 'id';
Empty set (0.00 sec)

2 방법 2: Information_schema[ | ]

SELECT 1 FROM Information_schema.columns
WHERE table_schema = 'DB명' 
AND table_name = '테이블명' 
AND column_name = '컬럼명'

해당 테이블이 있으면 1, 없으면 (결과없음)을 반환

3 방법 3: Information_schema[ | ]

SELECT EXISTS (
  SELECT 1 FROM Information_schema.columns
  WHERE table_schema = 'DB명' 
  AND table_name = '테이블명' 
  AND column_name = '컬럼명'
) AS flag

해당 테이블이 있으면 flag=1, 없으면 flag=0을 반환

4 방법 4: PHP[ | ]

$mysqli = new mysqli('localhost','my_user','my_password','world');
$result = $mysqli->query("SHOW COLUMNS FROM `테이블명` LIKE '컬럼명'");
$exist = $result->num_rows > 0;

5 같이 보기[ | ]

6 참고[ | ]

http://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql