Solution: set all encoding in utf8

(in mysql using utf8 not utf-8)

Step1: check your encoding of mysql

mysql> show variables like <span style="color: #800000">"</span><span style="color: #800000">character%</span><span style="color: #800000">"</span><span style="color: #000000">;
</span>+--------------------------+-----------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | /usr/local/mysql-<span style="color: #800080">5.7</span>.<span style="color: #800080">16</span>-osx10.<span style="color: #800080">11</span>-x86_64/share/charsets/ |
+--------------------------+---------------------------+
<span style="color: #800080">8</span> rows <span style="color: #0000ff">in</span> set (<span style="color: #800080">0.00</span> sec)

Maybe your terminal would display diff from mine. That‘s OK and you have to set default encoding by modify the "my.cnf" file of mysql.

Step2 : Modify default encoding of mysql

http://www.cnblogs.com/jinjidedale/p/6183451.html 

Step3: Change the database & table encoding

mysql><span style="color: #000000"> alter database testDB character set utf8;
Query OK, </span><span style="color: #800080">1</span> row affected (<span style="color: #800080">0.01</span><span style="color: #000000"> sec)

mysql</span>><span style="color: #000000"> alter table testTB convert to character set utf8;
Query OK, </span><span style="color: #800080">0</span> rows affected (<span style="color: #800080">0.06</span><span style="color: #000000"> sec)
Records: </span><span style="color: #800080">0</span>  Duplicates: <span style="color: #800080">0</span>  Warnings: <span style="color: #800080">0</span>

Check your database & table encoding now and remember to restart your mysql if necessary. 

 mysql> show create database testDB;

 +----------+-------------------+

 | Database | Create Database   |

 +----------+-------------------+

 | testDB   | CREATE DATABASE `testDB` /*!40100 DEFAULT CHARACTER SET utf8 */ |

 +----------+-------------------+

 1 row in set (0.00 sec)

mysql><span style="color: #000000"> show create table testTB;
</span>+--------+----------------+
| Table  | Create Table                                                                                              
+--------+----------------+
| testTB |<span style="color: #000000"> CREATE TABLE `testTB` (
  `testNum` varchar(</span><span style="color: #800080">16</span><span style="color: #000000">) NOT NULL,
  `testName` varchar(</span><span style="color: #800080">32</span><span style="color: #000000">) NOT NULL,
  `testDate` </span><span style="color: #0000ff">date</span><span style="color: #000000"> NOT NULL
) ENGINE</span>=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------+
<span style="color: #800080">1</span> row <span style="color: #0000ff">in</span> set (<span style="color: #800080">0.01</span> sec)

Step4: change import file encoding

If you insert data by loading data infile, you have to makes sure the file has the same encdoing of utf8. The files I get from windows cannot directly use for importing. Mostly the files have Chinese character are encoding with gbk. You can open the file by Safari and click "view-encoding" to change into gbk. [cmd+A][cmd+c] copy the file into a new file opened by "text editor" and set preference of storing by encoding utf-8 in "open&store". Then the new file is totally can view in utf-8 with Chinese characters.

Finally you can import data form "test.txt" instead of insert data one by one.

mysql> load data local infile <span style="color: #800000">"</span><span style="color: #800000">/Users/.../Desktop/test.txt</span><span style="color: #800000">"</span><span style="color: #000000"> into table testTB (testNum,testName,date);
Query OK, </span><span style="color: #800080">4</span> rows affected, <span style="color: #800080">1</span> warning (<span style="color: #800080">0.00</span><span style="color: #000000"> sec)
Records: </span><span style="color: #800080">4</span>  Deleted: <span style="color: #800080">0</span>  Skipped: <span style="color: #800080">0</span>  Warnings: <span style="color: #800080">1</span>

good luck!

 

Encoding of mysql for mac

标签：charset   date   cannot   safari   pre   import   convert   result   edit   

本文系统来源：http://www.cnblogs.com/22Kon/p/6713768.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！