最近学习sql语句,网上看到了一个将文本文件中的数据导入mysql数据库的方法
(1)/home/zhu/mysql_data数据如下:2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负(2)mysql> load data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);
然后输出如下:mysql> select * from temp;
+------------+---------+
| date | shengfu |
+------------+---------+
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
+------------+---------+
7 rows in set (0.00 sec)
胜负都是问号。(3)mysql> show create table temp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)(4)mysql> show full columns from temp;
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| date | varchar(10) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| shengfu | char(5) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)(5)mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)刚接触过mysql数据库,不知道怎么搞得,希望好心人帮忙,感激不尽!!
MySQL数据库
(1)/home/zhu/mysql_data数据如下:2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负(2)mysql> load data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);
然后输出如下:mysql> select * from temp;
+------------+---------+
| date | shengfu |
+------------+---------+
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
+------------+---------+
7 rows in set (0.00 sec)
胜负都是问号。(3)mysql> show create table temp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)(4)mysql> show full columns from temp;
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| date | varchar(10) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| shengfu | char(5) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)(5)mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)刚接触过mysql数据库,不知道怎么搞得,希望好心人帮忙,感激不尽!!
MySQL数据库
解决方案 »
- 使用c语言向mysql插入中文记录的问题
- 请问怎么么sql server下的存储过程转到mysql下的存储过程,语法有怎样的变化
- 计算不同分组下,累加时长。
- 存储引擎问题
- -------急问----mysql 存储过程异常处理------------路人请进------(附企业核心代码)-------------
- MySQL查询问题
- 请问,我下了一个MYSQL,请问我怎么知道它是源文件的还是二进制的?我刚刚刚学LINUX和MYSQL。
- mysql NOT IN ,!= 可以使用索引吗?
- MySQL和SQL Server如何做数据同步?
- Windows下没有生成慢查询日志
- MySQL创建delete触发器语法错误了
- mysql表的查询量
set names utf8;
CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
oad data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);
然后再导入。
我试了下, set names utf8,
然后在load data那句里, character set utf8,可以正常显示
用gbk就还是乱码。是不是看这个
| temp | CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) CHARACTER SET utf8 DEFAULT NULL //这里有个utf8
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |得出是设置成utf8?
"然后在load data那句里, character set utf8,可以正常显示
用gbk就还是乱码。"详细描述。
set names ‘gbk';
mysql> load data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);改成 set names 'utf8'
mysql> load data local infile '/home/zhu/mysql_data' into table temp character set utf8 fields terminated by ' ' (date, shengfu);
就没乱码问题
看一下下面的文章
数据库默认类型改为utf-8