[mysql] character set check, 변경

한글 데이터 insert시 에러가 발생하여 확인, 변경함

mysql> INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
ERROR 1366 (HY000): Incorrect string value: '\xEC\x9D\xB4\xEC\x8A\xB9...' for column 'name' at row 1

확인/변경

mysql> SHOW VARIABLES LIKE 'character_set_system';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_system | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> show create database sqlDB;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| sqlDB    | CREATE DATABASE `sqlDB` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use sqlDB;

mysql> show create table usertbl;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                            |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| usertbl | CREATE TABLE `usertbl` (
  `userid` char(20) NOT NULL,
  `name` varchar(20) NOT NULL,
  `birthyear` int(11) NOT NULL,
  `addr` char(20) NOT NULL,
  `mobile1` char(20) DEFAULT NULL,
  `mobile2` char(20) DEFAULT NULL,
  `height` smallint(6) DEFAULT NULL,
  `mdate` datetime DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database sqlDB default character set='utf8';
Query OK, 1 row affected (0.00 sec)

mysql> alter table usertbl default character set='utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table buytbl default character set='utf8';
Query OK, 0 rows affected (0.00 sec)