mysql データを読み込むとき「This command is not allowed with this MySQL version」

cvsファイルのデータをMySQLへ読み込む方法:

mysql> LOAD DATA LOCAL INFILE "ファイルの絶対パス/ファイル名.csv"

  INTO table テーブル名小文字 FIELDS TERMINATED BY ','

        ENCLOSED BY '"'

        ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

IGNORE 1 LINES;

ーーーーーーーーーー

メモ:

・ファイルの絶対パス:ファイルのあるフォルダをcd にして、 pwd とターミナルで打つ。すると、出て来る。

IGNORE 1 LINES; 項目名をデータベースに入れないでインポート。

ーーーーーーーーーーエラー対応

エラー:ERROR 1148 (42000): The used command is not allowed with this MySQL version

コード:

mysql> LOAD DATA LOCAL INFILE "/Users/HOkaniwa/gci/usercuisine.csv" INTO table cuisines FIELDS TERMINATED BY ','        ENCLOSED BY '"'        ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

ERROR 1148 (42000): The used command is not allowed with this MySQL version

対策:

LOCAL という言葉を入れない

結果:

mysql> LOAD DATA INFILE "/Users/HOkaniwa/gci/usercuisine.csv" INTO table cuisines FIELDS TERMINATED BY ','        ENCLOSED BY '"'        ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

 

参考:

Like most admins in unix/linux world we use the terminal, and when using the terminal this usually means, you are local to the box - ie., sitting at the physical keyboard of the mysql server OR you are using ssh to connect via port 22. For this issue of load data local infile, if given the error - ERROR 1148 (42000): The used command is not allowed with this MySQL version, remember, you are remotely connected, and file you wish to import is local already, THUS, simply remove the word LOCAL, as it is already local, and the error message might be a bit misleading.

MySQL :: MySQL 5.0 Reference Manual :: 3.3.3 Loading Data into a Table

 

 

他:

CSVのフィールドのがカンマ区切りの場合は
fields terminated by ‘,’

文字列にダブルクォーテーションが入っている場合はその後に
OPTIONALLY ENCLOSED BY ‘”‘

を設定します。

everyday-growth.com