MySQL字符集
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL字符集,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8889字,纯文字阅读大概需要13分钟。
内容图文
![MySQL字符集](/upload/InfoBanner/zyjiaocheng/921/dda011788d8943bfa63359f707f1eaf3.jpg)
MySQL字符集的问题
当查看数据是出现乱码该怎么处理?
mysql> select * from book.books limit 5;
+-----+-------------------------------------------------------+---------+--------------------------------------------------------------+-------+------------+-----------------------+------------+
| bId | bName | bTypeId | publishing | price | pubDate | author | ISBN |
+-----+-------------------------------------------------------+---------+--------------------------------------------------------------+-------+------------+-----------------------+------------+
| 1 | 网ç«?å?¶ä½?ç?´é€?车 | 2 | 电è?‘ç?±å¥½è€…æ?志社 | 34 | 2004-10-01 | è?—壮 | 7505380796 |
| 2 | 黑客�网�安全 | 6 | �空工���社 | 41 | 2002-07-01 | ��超 | 7121010925 |
| 3 | 网ç»?ç¨?åºä¸?设计ï¼asp | 2 | å?—方交é€?大å¦å?ºç‰?社 | 43 | 2005-02-01 | ç??ç?¥ | 75053815x |
| 4 | pagemaker 7.0ç?æ??å?¹è®æ??ç¨? | 9 | ä¸å?½ç”µå??å?ºç‰?社 | 43 | 2005-01-01 | å?å?©è?± | 7121008947 |
| 5 | 黑客攻å?»é?²è??ç§?ç¬? | 6 | å?—京腾å?¾ç”µåå?ºç‰?社 | 44 | 2003-06-29 | èµµé?·é?¨ | 7120000233 |
+-----+-------------------------------------------------------+---------+--------------------------------------------------------------+-------+------------+-----------------------+------------+
5 rows in set (0.00 sec)
查看mysql数据库服务器和数据库mysql字符集:
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| 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/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
查看mysql数据列的字符集:
mysql> show full columns from book.books;
+------------+------------------------------------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------+------------------------------------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| bId | int(4) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| bName | varchar(255) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| bTypeId | enum('1','2','3','4','5','6','7','8','9','10') | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| publishing | varchar(255) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| price | int(4) | NULL | YES | | NULL | | select,insert,update,references | |
| pubDate | date | NULL | YES | | NULL | | select,insert,update,references | |
| author | varchar(30) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| ISBN | varchar(255) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
+------------+------------------------------------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
8 rows in set (0.00 sec)
查看创建表时的字符集:
mysql> show create table book.books;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| books | CREATE TABLE `books` (
`bId` int(4) NOT NULL AUTO_INCREMENT,
`bName` varchar(255) DEFAULT NULL,
`bTypeId` enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL,
`publishing` varchar(255) DEFAULT NULL,
`price` int(4) DEFAULT NULL,
`pubDate` date DEFAULT NULL,
`author` varchar(30) DEFAULT NULL,
`ISBN` varchar(255) DEFAULT NULL,
PRIMARY KEY (`bId`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
数据库默认的字符集和表的字符集不一致,导致数据乱码。
解决办法:
把数据的字符集该为数据库默认的字符集就可以了。
导出表结构:
[root@tiandong ~]# mysqldump -uroot -p123456 --default-character-set=latin1 -d book > book2.sql --default-character-set=utf8
导出数据:
[root@tiandong ~]# mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 book > bookdata.sql
修改下面两个文件的字符集
[root@tiandong ~]# ll -trl |tail -n 2
-rw-r--r-- 1 root root 2355 Oct 14 14:03 book2.sql
-rw-r--r-- 1 root root 6444 Oct 14 14:12 bookdata.sql
创建一个新库book2,然后把数据导入:
mysql> create database book2;
[root@tiandong ~]# mysql -uroot -p123456 book2< book2.sql
[root@tiandong ~]# mysql -uroot -p123456 book2< bookdata.sql
然后再次查看:
mysql> select * from books limit 5;
+-----+---------------------------------+---------+-----------------------------+-------+------------+-----------+------------+
| bId | bName | bTypeId | publishing | price | pubDate | author | ISBN |
+-----+---------------------------------+---------+-----------------------------+-------+------------+-----------+------------+
| 1 | 网站制作直通车 | 2 | 电脑爱好者杂志社 | 34 | 2004-10-01 | 苗壮 | 7505380796 |
| 2 | 黑客与网络安全 | 6 | 航空工业出版社 | 41 | 2002-07-01 | 白立超 | 7121010925 |
| 3 | 网络程序与设计-asp | 2 | 北方交通大学出版社 | 43 | 2005-02-01 | 王玥 | 75053815x |
| 4 | pagemaker 7.0短期培训教程 | 9 | 中国电力出版社 | 43 | 2005-01-01 | 孙利英 | 7121008947 |
| 5 | 黑客攻击防范秘笈 | 6 | 北京腾图电子出版社 | 44 | 2003-06-29 | 赵雷雨 | 7120000233 |
+-----+---------------------------------+---------+-----------------------------+-------+------------+-----------+------------+
5 rows in set (0.00 sec)
此时数据护肤正常了。
内容总结
以上是互联网集市为您收集整理的MySQL字符集全部内容,希望文章能够帮你解决MySQL字符集所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。