[size=medium]今天在自己的window8.1系统上安装了Mysql v5.0,完成后用Navicat for MySQL连接了一下,并在默认的test库下创建表插入数据。结果在插入数据的时候有如下信息[/size]
[size=medium]这段sql语句我很久之前执行过,并无问题,于是开始查找原因
有人说可能是数据库编码的问题,于是查了下编码:[/size]
[size=medium]这里编码是latin1编码
更改数据库编码[/size]
[size=medium]再次查看test库编码[/size]
[size=medium]重新执行sql语句[/size]
[size=medium][color=red]结论:是数据库编码导致了[Err] 1406 - Data too long for column 't_name' at row 1[/color][/size]
[SQL] DROP TABLE IF EXISTS t_project;
受影响的行: 0
时间: 0.000ms
[SQL]
CREATE TABLE t_project (
t_id int(11) NOT NULL AUTO_INCREMENT,
t_no char(8) DEFAULT NULL,
t_name varchar(50) DEFAULT NULL,
t_start_date date DEFAULT NULL,
t_end_date date DEFAULT NULL,
PRIMARY KEY (t_id)
) ENGINE=InnoDB ;
受影响的行: 0
时间: 0.354ms
[SQL]
INSERT INTO t_project
VALUES (97,'pm000001','上山寻找老虎','2008-01-02','2008-01-15');
[Err] 1406 - Data too long for column 't_name' at row 1
[size=medium]这段sql语句我很久之前执行过,并无问题,于是开始查找原因
有人说可能是数据库编码的问题,于是查了下编码:[/size]
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)
[size=medium]这里编码是latin1编码
更改数据库编码[/size]
mysql> alter database test CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
[size=medium]再次查看test库编码[/size]
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | utf8 |
+------------------------+-------+
1 row in set (0.00 sec)
[size=medium]重新执行sql语句[/size]
mysql> CREATE TABLE t_project (
-> t_id int(11) NOT NULL AUTO_INCREMENT,
-> t_no char(8) DEFAULT NULL,
-> t_name varchar(50) DEFAULT NULL,
-> t_start_date date DEFAULT NULL,
-> t_end_date date DEFAULT NULL,
-> PRIMARY KEY (t_id)
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t_project
-> VALUES (97,'pm000001','上山寻找老虎','2008-01-02','2008-01-15');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t_project
-> VALUES (98,'pm000002','上班赚钱喂老虎吃肉','2008-02-01','2008-02-20');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t_project
-> VALUES (99,'pm000003','骑着老虎上街溜达','2008-03-01','2008-03-08');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO t_project
-> VALUES (100,'pm000004','让老虎咬我不喜欢的人','2008-03-08','2008-03-10');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO t_project
-> VALUES (167,'pm000071','name0968058634','2008-05-28','2008-08-26');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO t_project
-> VALUES (168,'pm000072','name1255323646','2008-11-05','2009-01-17');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO t_project
-> VALUES (197,'pm000101','和老虎喝着咖啡彻夜长谈','2008-01-01','2008-02-02'
);
Query OK, 1 row affected (0.04 sec)
mysql> #
mysql> # Table structure for table t_user
mysql> #
mysql> DROP TABLE IF EXISTS t_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE t_user (
-> t_id int(11) NOT NULL AUTO_INCREMENT,
-> t_loginname varchar(50) DEFAULT NULL,
-> t_password varchar(50) DEFAULT NULL,
-> t_name varchar(100) DEFAULT NULL, ##真实姓名
-> PRIMARY KEY (t_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO t_user(t_loginname, t_password, t_name)
-> VALUES ('dachui','12345','goodman');
Query OK, 1 row affected (0.06 sec)
[size=medium][color=red]结论:是数据库编码导致了[Err] 1406 - Data too long for column 't_name' at row 1[/color][/size]