SET NAMES 'gb2312'drop table if exists t_course;
create table t_course(
id int(11) not null auto_increment,
name varchar(50) default null,
primary key(id)
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
#set names 'gbk';
insert into t_course values (1,'java');
insert into t_course values (2,'.Net');
insert into t_course values (3,'PHP');
insert into t_course values (4,'框架');drop table if exists t_subject;
create table t_subject(
id int(11) not null auto_increment,
name varchar(50) default null,
course_id int(11) default null,
primary key(id)
)DEFAULT CHARSET=utf8;
insert into t_subject values (1,'jsp',1);
insert into t_subject values (2,'servlet',1);
insert into t_subject values (3,'struts',1);
insert into t_subject values (4,'c#',2);
insert into t_subject values (5,'ASP.NET',2);
insert into t_subject values (6,'VB.NET',2);
insert into t_subject values (7,'php',3);
insert into t_subject values (8,'mysql',3);
insert into t_subject values (9,'hibernate框架',4);
insert into t_subject values (10,'spring框架',4);导入正常,可是查询时全显示:Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'inser
t into t_subject values (10,'spring妗嗘灦',4)' at line 2
mysql> source e:\db.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'drop
table if exists t_course' at line 3
ERROR 1050 (42S01): Table 't_course' already exists
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
Query OK, 0 rows affected (0.03 sec)Query OK, 0 rows affected (0.06 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
mysql> source e:\db.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'drop
table if exists t_course' at line 3
ERROR 1050 (42S01): Table 't_course' already exists
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
Query OK, 0 rows affected (0.02 sec)Query OK, 0 rows affected (0.08 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.05 sec)Query OK, 1 row affected (0.05 sec)ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
mysql> drop table t_course;
Query OK, 0 rows affected (0.03 sec)mysql> drop table t_subject;
Query OK, 0 rows affected (0.03 sec)mysql> source e:\db.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'drop
table if exists t_course' at line 3
Query OK, 0 rows affected (0.06 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.05 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
mysql> select * from t_subject;
+----+---------+-----------+
| id | name | course_id |
+----+---------+-----------+
| 1 | jsp | 1 |
| 2 | servlet | 1 |
| 3 | struts | 1 |
| 4 | c# | 2 |
| 5 | ASP.NET | 2 |
| 6 | VB.NET | 2 |
| 7 | php | 3 |
| 8 | mysql | 3 |
+----+---------+-----------+
8 rows in set (0.00 sec)mysql> select * from t_course;
+----+--------+
| id | name |
+----+--------+
| 1 | java |
| 2 | .Net |
| 3 | PHP |
| 4 | 妗嗘灦 |
+----+--------+
4 rows in set (0.02 sec)mysql> source e:\db.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'drop
table if exists t_course' at line 3
ERROR 1050 (42S01): Table 't_course' already exists
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.06 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.06 sec)ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
mysql> drop table t_subject;
Query OK, 0 rows affected (0.01 sec)mysql> drop table t_course;
Query OK, 0 rows affected (0.00 sec)mysql> source e:\db.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'drop
table if exists t_course' at line 3
Query OK, 0 rows affected (0.06 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.02 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.06 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
mysql> drop table t_course;
Query OK, 0 rows affected (0.01 sec)mysql> drop table t_course;
ERROR 1051 (42S02): Unknown table 't_course'
mysql> drop table t_subject;
Query OK, 0 rows affected (0.01 sec)mysql> source e:\db.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'drop
table if exists t_course' at line 3
Query OK, 0 rows affected (0.08 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.05 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.00 sec)mysql> select * from t_subject;
+----+-----------------+-----------+
| id | name | course_id |
+----+-----------------+-----------+
| 1 | jsp | 1 |
| 2 | servlet | 1 |
| 3 | struts | 1 |
| 4 | c# | 2 |
| 5 | ASP.NET | 2 |
| 6 | VB.NET | 2 |
| 7 | php | 3 |
| 8 | mysql | 3 |
| 9 | hibernate妗嗘灦 | 4 |
| 10 | spring妗嗘灦 | 4 |
+----+-----------------+-----------+
10 rows in set (0.00 sec)mysql> select * from t_course;
+----+--------+
| id | name |
+----+--------+
| 1 | java |
| 2 | .Net |
| 3 | PHP |
| 4 | 妗嗘灦 |
+----+--------+
4 rows in set (0.00 sec)mysql>这个问题如何解决,网上给了很多,我试过没能成功,望大家指点一二
MySQL 中文显示乱码
set names 'gbk';drop table if exists t_course;create table t_course(id int(11) not null auto_increment,name varchar(50) default null,primary key(id))DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci ;#set names 'gbk';insert into t_course values (1,'java');insert into t_course values (2,'.Net');insert into t_course values (3,'PHP');insert into t_course values (4,'框架');drop table if exists t_subject;create table t_subject(id int(11) not null auto_increment,name varchar(50) default null,course_id int(11) default null,primary key(id))DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci ;insert into t_subject values (1,'jsp',1);insert into t_subject values (2,'servlet',1);insert into t_subject values (3,'struts',1);insert into t_subject values (4,'c#',2);insert into t_subject values (5,'ASP.NET',2);insert into t_subject values (6,'VB.NET',2);insert into t_subject values (7,'php',3);insert into t_subject values (8,'mysql',3);insert into t_subject values (9,'hibernate框架',4);insert into t_subject values (10,'spring框架',4);select * from t_course;select * from t_subject;
执行后
+----+--------+
| id | name |
+----+--------+
| 1 | java |
| 2 | .Net |
| 3 | PHP |
| 4 | 妗嗘灦 |
+----+--------+
4 rows in set (0.00 sec)+----+-----------------+-----------+
| id | name | course_id |
+----+-----------------+-----------+
| 1 | jsp | 1 |
| 2 | servlet | 1 |
| 3 | struts | 1 |
| 4 | c# | 2 |
| 5 | ASP.NET | 2 |
| 6 | VB.NET | 2 |
| 7 | php | 3 |
| 8 | mysql | 3 |
| 9 | hibernate妗嗘灦 | 4 |
| 10 | spring妗嗘灦 | 4 |
+----+-----------------+-----------+
10 rows in set (0.00 sec)我的数据库编码变量为:+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | gbk
|
| character_set_connection | gbk
|
| character_set_database | gbk
|
| character_set_filesystem | binary
|
| character_set_results | gbk
|
| character_set_server | gbk
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
show create table t_course;
| t_course | CREATE TABLE `t_course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk |
show full columns from t_course;
| Field | Type | Collation | Null | Key | Default | Extra |
Privileges | Comment |
+-------+-------------+----------------+------+-----+---------+----------------+
---------------------------------+---------+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment |
select,insert,update,references | |
| name | varchar(50) | gbk_chinese_ci | YES | | NULL | |
select,insert,update,references |
谁能帮我分析分析问题出在哪儿了?谢谢
Query OK, 0 rows affected (0.00 sec)mysql> create table t_course(
-> id int(11) not null auto_increment,
-> name varchar(50) default null,
-> primary key(id)
-> )DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci ;
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> insert into t_course values (3,'PHP');
Query OK, 1 row affected (0.01 sec)mysql> insert into t_course values (4,'框架');
Query OK, 1 row affected (0.05 sec)mysql>
mysql> create table t_subject(
-> id int(11) not null auto_increment,
-> name varchar(50) default null,
-> course_id int(11) default null,
-> primary key(id)
-> )DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci ;
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> insert into t_subject values (7,'php',3);
Query OK, 1 row affected (0.03 sec)mysql> insert into t_subject values (8,'mysql',3);
Query OK, 1 row affected (0.02 sec)mysql> insert into t_subject values (9,'hibernate框架',4);
Query OK, 1 row affected (0.03 sec)mysql> insert into t_subject values (10,'spring框架',4);
Query OK, 1 row affected (0.02 sec)mysql>
mysql>
mysql> select * from t_course;
+----+------+
| id | name |
+----+------+
| 3 | PHP |
| 4 | 框架 |
+----+------+
2 rows in set (0.00 sec)mysql> select * from t_subject;
+----+---------------+-----------+
| id | name | course_id |
+----+---------------+-----------+
| 7 | php | 3 |
| 8 | mysql | 3 |
| 9 | hibernate框架 | 4 |
| 10 | spring框架 | 4 |
+----+---------------+-----------+
4 rows in set (0.00 sec)mysql>
character_set_connection
character_set_client
character_set_result
和
character_set_database(如果没有设置)
保持一致就好了。前三个变量可以用set names character_set_name来设置。
后面的可以用设置变量或者设置默认设置来配置。
检查的时候可以检查这些变量是否匹配。我是根据我的经验说的,不一定对……
整理:utf8_general_ci
在字符从数据表读出时,用gb2312,是不乱码的
不是数据库数据集的问题建议看下版主的帖子
然后在进行插入数据
n 'name' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\xA1\x86\xE6\x9E\xB6' for colum
n 'name' at row 1
---------------------
像这种,都是由于字符集不匹配而引起的,仔细调整下字符集编码问题是可以解决的。
很好啊