在定义字段的时候设字符集 的优先机别更高

 

| articles | CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 |
mysql> select * from articles;
+----+---------+
| id | content |
+----+---------+
| 11 | ha      |
| 12 | xi      |
| 13 | ai      |
| 14 | ho      |
| 15 | ??      |
+----+---------+
5 rows in set (0.01 sec)
mysql> alter table articles default character set utf8;
Query OK, 5 rows affected (0.38 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from articles;
+----+---------+
| id | content |
+----+---------+
| 11 | ha      |
| 12 | xi      |
| 13 | ai      |
| 14 | ho      |
| 15 | ??      |
+----+---------+
5 rows in set (0.01 sec)
| articles | CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` longtext CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
mysql> insert into articles (id,content) values (16,'修改字符集');
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> select * from articles;
+----+---------+
| id | content |
+----+---------+
| 11 | ha      |
| 12 | xi      |
| 13 | ai      |
| 14 | ho      |
| 15 | ??      |
| 16 | ?????   |
+----+---------+
6 rows in set (0.00 sec)
＃但问题还在，好像只有删除了，重新导入数据才会生效！明白了建表的时候，字段定义时用的是：
 `content` longtext CHARACTER SET latin1 NOT NULL,

mysql> insert into articles(id,content) values(1,'林'),(2,'陈');
Query OK, 2 rows affected, 2 warnings (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select * from articles;
+----+---------+
| id | content |
+----+---------+
|  1 | ?       |
|  2 | ?       |
+----+---------+
2 rows in set (0.00 sec)

 

 

mysql> drop table articles;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE `articles` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `content` longtext  NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into articles(id,content) values(1,'林'),(2,'陈');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from articles;
+----+---------+
| id | content |
+----+---------+
|  1 | 林      |
|  2 | 陈      |
+----+---------+
2 rows in set (0.00 sec)