incorrect string value MySql中文字符报错

时间:2021-07-19 07:09:11

--查看字符集


mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | latin1                                                   |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set

mysql>

 

--举个例子merchinfo

ALTER TABLE `merchinfo` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE `merchinfo` CHANGE `MerchName` `MerchName` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

ALTER DATABASE `market` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

 

 

--再看看字符集
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database | lutf8                                                   |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set

 

mysql> select * from merchinfo ;
+---------+----------------+------------+----------+------------+---------+---------------+---------------+---------------+---------------+------------+-----------+-----------+-----------+
| MerchID | MerchName      | MerchPrice | MerchNum | CautionNum | PlanNum | BarCode       | SalesProPrice | SalesProDateS | SalesProDateE | AllowAbate | AllowSale | FactoryID | ProvideID |
+---------+----------------+------------+----------+------------+---------+---------------+---------------+---------------+---------------+------------+-----------+-----------+-----------+
|       1 | 挺美内衣       |         99 |       10 |          5 |     100 | 6931748861834 |            99 | NULL          | NULL          |          1 |         1 | 1111      | 1231      |
|       2 | 营养快线       |          4 |       10 |          5 |     300 | 6931748861835 |             3 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|       3 | 双子座乒乓球   |          1 |       20 |         10 |     100 | 6931748861836 |             1 | NULL          | NULL          |          1 |         1 | 1222      | 1233      |
|       4 | 老坛酸菜方便面 |          3 |       14 |         10 |       2 | 6931748861833 |             2 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|       5 | 书乐文具橡皮擦 |          1 |       10 |          5 |     100 | 6931748861832 |             1 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|       6 | 英语作文本     |          4 |       10 |          5 |     300 | 6931748861837 |             3 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|       7 | 康师傅矿泉水   |          1 |       20 |         10 |     100 | 6931748861838 |             1 | NULL          | NULL          |          1 |         1 | 1222      | 1233      |
|       8 | 大话通信书     |          3 |       14 |         10 |       2 | 6931748861839 |             2 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|       9 | 党员文摘       |          1 |       10 |          5 |     100 | 6931748861840 |             1 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|      10 | 程序员         |          4 |       10 |          5 |     300 | 6931748861841 |             3 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|      11 | 电子紧急       |          1 |       20 |         10 |     100 | 6931748861842 |             1 | NULL          | NULL          |          1 |         1 | 1222      | 1233      |
|      12 | 浙江嘉信粽子   |          3 |       14 |         10 |       2 | 6931748861843 |             2 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|      13 | 皮毛手套       |          1 |       10 |          5 |     100 | 6931748861844 |             1 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |
|      14 | 玩具           |          4 |       10 |          5 |     300 | 6931748861845 |             3 | NULL          | NULL          |          1 |         1 | 1222      | 1231      |