如果是导入有中文的数据,我的mysql 设置的utf8 字符集,所以你要导入的xxx.txt 文件也要保存utf-8的字符集,命令 load data infile "d:/Websites/Sxxxx/test1.txt" ignore into table `names` fields terminated by ',' enclosed by '"';
不知道用replace 这个关键字的话,还是会乱码。。不同、等高手回答。
在详细的介绍,推荐大家去看mysql手册去吧、里面介绍的很详细、
在使用LOAD DATA到MySQL的时候,有2种情况:
(1)在远程客户端(需要添加选项:--local-infile=1)导入远程客户端文本到MySQL,需指定LOCAL(默认就是ignore),加ignore选项会放弃数据,加replace选项会更新数据,都不会出现唯一性约束问题。
(2)在本地服务器导入本地服务器文本到MySQL,不指定LOACL,出现唯一性约束冲突,会失败回滚,数据导入不进去,这个时候就需要加ignore或者replace来导入数据。
测试如下:
(1)本地服务器导入本地服务器文本
mysql> show create table tmp_loaddata\G;
*************************** 1. row ***************************
Table: tmp_loaddata
Create Table:CREATE TABLE `tmp_loaddata` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>select * from tmp_loaddata;
+----+------+
| id | name |
+----+------+
|1 | test |
+----+------+
1 row in set (0.00 sec)
mysql>
mysql>system cat /home/zhuxu/1.txt
1,new update
2,new update
mysql>
mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#出现唯一性约束冲突,会失败回滚
mysql>select * from tmp_loaddata;
+----+------+
| id | name |
+----+------+
|1 | test |
+----+------+
1 row in set (0.00 sec)
mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' IGNOREINTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
Query OK,1 row affected(0.00 sec)
Records: 2Deleted: 0Skipped: 1Warnings: 0
#使用IGNORE对于冲突的数据丢弃掉。
mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | test|
|2 | new update |
+----+------------+
2 rows in set (0.00 sec)
mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' REPLACEINTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
Query OK,3 rows affected(0.00 sec)
Records: 2Deleted: 1Skipped: 0Warnings: 0
#使用REPLACE对于冲突的数据进行更新。
mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | new update |
|2 | new update |
+----+------------+
2 rows in set (0.00 sec)
(2)远程客户端导入远程客户端文本
[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.47-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>select * from tmp_loaddata;
+----+------+
| id | name |
+----+------+
|1 | test |
+----+------+
1 row in set (0.00 sec)
mysql>system cat /tmp/2.txt
1,new update
2,new update
3,new update
mysql>
mysql>LOAD DATA INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
ERROR 13 (HY000): Can't get stat of '/tmp/2.txt' (Errcode: 2)
#由于数据库服务器没有对应的文本文件,所以报错。
mysql>
mysql>LOAD DATA LOCALINFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
#进去mysql远程客户端,还需要加--local-infile=1参数指定。
mysql> exit
Bye
[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \
> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";
--------------
LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','
--------------
Query OK,2 rows affected(0.00 sec)
Records: 3Deleted: 0Skipped: 1Warnings: 0
Bye
mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | test|
|2 | new update |
|3 | new update |
+----+------------+
3 rows in set (0.00 sec)
#
[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \
> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNOREINTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";
--------------
LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','
--------------
Query OK,0 rows affected(0.00 sec)
Records: 3Deleted: 0Skipped: 3Warnings: 0
Bye
mysql>select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | test|
|2 | new update |
|3 | new update |
+----+------------+
3 rows in set (0.00 sec)
#
[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \
> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACEINTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";
--------------
LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','
--------------
Query OK,4 rows affected(0.00 sec)
Records: 3Deleted: 1Skipped: 0Warnings: 0
Bye
mysql> select * from tmp_loaddata;
+----+------------+
| id | name|
+----+------------+
|1 | new update |
|2 | new update |
|3 | new update |
+----+------------+
3 rows in set (0.00 sec)
--EOF--
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
RTX 5090要首发 性能要翻倍!三星展示GDDR7显存
三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。
首次推出的GDDR7内存模块密度为16GB,每个模块容量为2GB。其速度预设为32 Gbps(PAM3),但也可以降至28 Gbps,以提高产量和初始阶段的整体性能和成本效益。
据三星表示,GDDR7内存的能效将提高20%,同时工作电压仅为1.1V,低于标准的1.2V。通过采用更新的封装材料和优化的电路设计,使得在高速运行时的发热量降低,GDDR7的热阻比GDDR6降低了70%。
更新日志
- [ABC]安娜-胆麦发烧女声[6N纯银镀膜][2016[低速原抓WAV+CUE]
- 任天堂今晚举行直面会!第三方及独立游戏展示
- 《哆啦A梦的铜锣烧店物语》发售!开罗公式+哆啦A梦
- 任天堂公布《塞尔达传说》系列时间线:野炊与王泪独立在外
- 五条人.2012-一些风景2CD【刀马旦】【WAV+CUE】
- 陈奕迅.2013-Easons.Life演唱会2CD(2024环球红馆40复刻系列)【环球】【WAV+CUE】
- 许美静.1995-遗憾(新马版)【上华】【WAV+CUE】
- 《叶倩文 歌声情缘》[WAV+CUE][410MB]
- 《张国荣 首首动听经典不容错过 追忆的风 2CD》[WAV+CUE][870MB]
- 《腾格尔 容中尔甲 亚东 高原三星 男人篇 3CD》[WAV/分轨][1GB]
- 命运圣契公测实测可用兑换码大全 命运圣契最新兑换码分享
- 黑神话悟空上品疾蝠精魄获取方法一览|上品疾蝠精魄收集攻略
- 《七龙珠电光炸裂!ZERO》GT角色预告片曝光,15位新角色登场
- [ABC]安娜-胆麦发烧女声[6N纯银镀膜][2016[低速原抓WAV+CUE]
- NewViennaOctetViennaWindSoloists-TheDeccaRecordings(2024)18CD[24-48][FLAC]-7