mysql load 從檔案讀入資料提示ERROR 1148

標籤：mysql   load data   error 1148   command is not allow   

建立資料表

<span style="font-size:18px;">CREATE TABLE weblogs(            md5  varchar(32),            url varchar(64),            request_date date,            request_time time,            ip varchar(15))</span>

我的版本是：Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)

在使用如下命令匯入時會報錯：

<span style="font-size:18px;">mysql> LOAD  DATA LOCAL INFILE '/home/hadoop/weblog_entries.txt' INTO table weblogs FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';ERROR 1148 (42000): The used command is not allowed with this MySQL version</span>

提示這個版本的mysql不支援這樣匯入

解決辦法：http://dev.mysql.com/doc/refman/5.5/en/load-data-local.html

<span style="font-size:18px;">[email protected]:~$ mysql -uroot<span style="background-color: rgb(255, 0, 0);"> --local-infile=1</span>  -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 45Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use realworld;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> <span style="color:#FF0000;">LOAD  DATA LOCAL INFILE '/home/hadoop/weblog_entries.txt' INTO table weblogs FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'</span>;Query OK, 3000 rows affected (1.17 sec)Records: 3000  Deleted: 0  Skipped: 0  Warnings: 0</span>

在串連mysql的時候加上--local-infile的參數 然後匯入即可

或者使用如下方式：

<span style="font-size:18px;">mysql -u [youruser] -h [youraddress] -p [yourpassword] [yourdatabase] --local-infile=1 -e "[yourcmd]" </span>

<span style="font-size:18px;">mysql -uroot -p123456 realworld --local-infile=1 -e "LOAD DATA local INFILE '/home/hadoop/weblog_entries.txt' INTO table weblogs FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'"</span>

原因分析：根據官方的解釋是mysql在編譯的時候預設把local-infile的參數設為0，就是關閉了從本地load的功能，所以如果需要使用只能自己開啟 通過連結的時候把該參數設定為1的方式

另外windows下面的分行符號和linux的是不一樣的

windows換行是\r\n，十六進位數值是：0D0A。
LINUX換行是\n，十六進位數值是：0A

mysql load 從檔案讀入資料提示ERROR 1148