56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> show create table t_sql \G;
*************************** 1. row ***************************
Table: t_sql
Create Table: CREATE TABLE `t_sql` (
`phone` varchar(6) DEFAULT NULL,
`addr` varchar(8) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> load data infile 'c:\\mysql.txt'
-> into table t_sql (@xx)
-> set phone=SUBSTRING(@xx,1,6),
-> addr=SUBSTRING(@xx,7,2),
-> email=SUBSTRING(@xx,9);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t_sql \G;
*************************** 1. row ***************************
phone: 锘?2345
addr: 6娴?
email: 鍗梛ava_boy@126.com
*************************** 2. row ***************************
phone: 468872
addr: 缇庡浗
email: java_boy@163.com
*************************** 3. row ***************************
phone: 985435
addr: 浜戝崡
email: sinaboy@126.com
*************************** 4. row ***************************
phone: 036597
addr: 闈炴床
email: java_boy@sina.com
*************************** 5. row ***************************
phone: 698743
addr: 娉板畨
email: java@vip.com
*************************** 6. row ***************************
phone: 456987
addr: 涓婃捣
email: testsqk@nba.com
6 rows in set (0.00 sec)
*************************** 1. row ***************************
phone: 锘?2345
addr: 6娴?
email: 鍗梛ava_boy@126.com
mysql> select * from t_sql \G;
*************************** 1. row ***************************
phone: ?22345
addr: 6济
email: 南java_boy@126.com
*************************** 2. row ***************************
phone: 468872
addr: 美国
email: java_boy@163.com
*************************** 3. row ***************************
phone: 985435
addr: 云南
email: sinaboy@126.com
*************************** 4. row ***************************
phone: 036597
addr: 非洲
email: java_boy@sina.com
*************************** 5. row ***************************
phone: 698743
addr: 泰安
email: java@vip.com
*************************** 6. row ***************************
phone: 456987
addr: 上海
email: testsqk@nba.com
*************************** 7. row ***************************
phone: ?22345
addr: 6济
email: 南java_boy@126.com
*************************** 8. row ***************************
phone: 468872
addr: 美国
email: java_boy@163.com
*************************** 9. row ***************************
phone: 985435
addr: 云南
email: sinaboy@126.com
*************************** 10. row ***************************
phone: 036597
addr: 非洲
email: java_boy@sina.com
*************************** 11. row ***************************
phone: 698743
addr: 泰安
email: java@vip.com
*************************** 12. row ***************************
phone: 456987
addr: 上海
email: testsqk@nba.com
12 rows in set (0.00 sec)
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 | C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> \s
--------------
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe Ver 14.14 Distrib 5.1.35, for Win32 (ia32)
Connection id: 1
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.35-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 2635 days 18 hours 28 min 28 sec
Threads: 3 Questions: 210 Slow queries: 0 Opens: 46 Flush tables: 1 Open tables: 1 Queries per second avg: 0.0
--------------
mysql> load data infile 'C:\\Users\\liuyann\\TEMP\\mysql.txt'
-> into table t_sql (@xx)
-> set phone=SUBSTRING(@xx,1,6),
-> addr=SUBSTRING(@xx,7,2),
-> email=SUBSTRING(@xx,9);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> select * from t_sql;
+--------+------+----------------------+
| phone | addr | email |
+--------+------+----------------------+
|23456 | 济 | 南java_boy@126.com
|68872 | 美 | 国java_boy@163.com
|5435 | 云 | 南sinaboy@126.com
|036597 | 非 | 洲java_boy@sina.com
|3 | 泰 | 安java@vip.com
| 456987 | 上 | 海testsqk@nba.com |
+--------+------+----------------------+
6 rows in set (0.00 sec)
mysql>