MySQL には、テーブルを CSV ファイルにエクスポートする機能があります。 CSV ファイル形式は、Microsoft Excel、Goole Docs、Open Office などのさまざまなアプリケーション間でデータを交換するために使用されるカンマ区切りの値です。 MySQL データを CSV ファイル形式で保存すると、必要な方法で分析してフォーマットできるため便利です。これは、データを非常に簡単にエクスポートするのに役立つプレーンテキスト ファイルです。
MySQL は、データベース サーバーにある CSV ファイルにテーブルを簡単にエクスポートする方法を提供します。 MySQL データをエクスポートする前に、次のことを確認する必要があります。
- MySQL サーバーのプロセスには、CSV ファイルを含む指定された (ターゲット) フォルダーへの読み取り/書き込みアクセス権があります。
- 指定された CSV ファイルはシステム内に存在してはなりません。
テーブルを CSV ファイルにエクスポートするには、 ....OUTFILE に選択 声明。この発言は褒め言葉です データを読み込む このコマンドは、テーブルからデータを書き込み、それをサーバー ホスト上の指定されたファイル形式にエクスポートするために使用されます。これは、この構文を使用するためのファイル権限があることを確認するためです。
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
この構文を値ステートメントとともに使用して、データをファイルに直接エクスポートすることもできます。次のステートメントはそれをより明確に説明しています。
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
輸出したい場合 テーブルのすべての列 , 以下の構文を使用します。このステートメントを使用すると、行の順序と数が制御されます。 注文方法 そして リミット 句。
連結Java文字列
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
以上のことから、
「,」で終わる行 : カンマ演算子で終了するファイル内の行の行を示すために使用されます。ファイル内の各行には、各列のデータが含まれます。
''で囲まれたフィールド : ファイルのフィールドをダブルクォーテーションで囲んで指定する場合に使用します。これにより、値にカンマ区切り文字が含まれることが防止されます。値が二重引用符で囲まれている場合、カンマは区切り文字として認識されません。
エクスポートされたファイルの保存場所
MySQL 内のエクスポートされたすべてのファイルの保存場所は、デフォルトの変数に保存されます。 secure_file_priv 。以下のコマンドを実行して、エクスポートされたファイルのデフォルトのパスを取得できます。
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
実行後、このパスが表示されるように、次のような結果が得られます。 C:/プログラムデータ/MySQL/MySQL サーバー 8.0/アップロード/ デフォルトのファイルの場所として。このパスは、エクスポート コマンドの実行時に使用されます。
で指定した CSV ファイルのデフォルトのエクスポート場所を変更したい場合は、 secure_file_priv 変数を編集する必要があります 私の.ini 設定ファイル。 Windows プラットフォームでは、このファイルは次のパスにあります。 C:ProgramDataMySQLMySQL サーバー X.Y 。
スキャナー.ネクストJava
MySQL データをエクスポートしたい場合は、まず、 データベース 少なくとも 1 つ テーブル 。この表を例として使用します。
作成できます データベースとテーブル 使用しているエディターで以下のコードを実行します。
CREATE DATABASE testdb; USE testdb; CREATE TABLE employee_detail ( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL, Phone varchar(15) DEFAULT NULL, City varchar(25) DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY unique_email (Email), UNIQUE KEY index_name_phone (Name,Phone) ) INSERT INTO employee_detail ( Id, Name, Email, Phone, City) VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'), (2, 'Suzi', '[email protected]', '70679834522', 'California'), (3, 'Joseph', '[email protected]', '09896765374', 'Alaska'), (4, 'Alex', '[email protected]', '97335737548', 'Los Angeles'), (5, 'Mark', '[email protected]', '78765645643', 'Washington'), (6, 'Stephen', '[email protected]', '986345793248', 'New York');
を実行すると、 選択する ステートメントを実行すると、次の出力が表示されます。
SELECT INTO ... OUTFILE ステートメントを使用して MySQL データを CSV 形式でエクスポートする
テーブル データを CSV ファイルにエクスポートするには、次のようにクエリを実行する必要があります。
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
次の出力が得られ、6 行が影響を受けていることがわかります。これは、指定されたテーブルに 6 行しか含まれていないためです。
それ以外の場合はJava
同じステートメントを再度実行すると、MySQL は次の出力に示すエラー メッセージを生成します。
エラー メッセージは、指定されたファイル名が指定された場所に既に存在することを示します。したがって、同じ名前と場所で新しい CSV ファイルをエクスポートすると、CSV ファイルを作成できません。これを解決するには、指定した場所にある既存のファイルを削除するか、ファイル名を変更して同じ場所にファイルを作成します。
次のように指定されたパスに移動することで、CSV ファイルが指定された場所に作成されたかどうかを確認できます。
このファイルを開くと、以下の画像のようになります。
この画像では、数値フィールドが引用符で囲まれていることがわかります。追加することでこのスタイルを変更できます ENCLOSED BY の前の OPTIONALLY 句 :
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
列見出し付きのデータのエクスポート
ファイルを便利にするために、列見出しとともにデータをエクスポートしたい場合があります。 CSV ファイルの最初の行に列見出しが含まれていると、エクスポートされたファイルがよりわかりやすくなります。列見出しを追加するには、 すべてを結合する 次のような声明:
SELECT 'Id', 'Name', 'Email', 'Phone', 'City' UNION ALL SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ';' ENCLOSED BY ''' ESCAPED BY ''' LINES TERMINATED BY ' ';
このクエリでは、すべての列名に見出しが追加されていることがわかります。指定された URL に移動すると、出力を確認できます。最初の行には各列の見出しが含まれています。
CSV 形式で MySQL テーブルをエクスポート
MySQL OUTFILE を使用すると、カラム名を指定せずにテーブルをエクスポートすることもできます。以下の構文を使用して、テーブルを CSV ファイル形式でエクスポートできます。
TABLE employee_detail ORDER BY City LIMIT 1000 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
上記のステートメントを実行すると、コマンドライン ツールは次の結果を生成します。これは、指定されたテーブルに 6 行が含まれていることを意味します。 従業員_バックアップ.csv ファイル。
アミシャ・パテル
Null値の処理
結果セットのフィールドに NULL 値が含まれる場合があり、その場合、ターゲット ファイル (エクスポートされたファイル タイプ) には NULL ではなく N が含まれます。 NULL 値を次のように置き換えることで、この問題を解決できます。 「該当なし（N/A）」 を使用して IFNULL 関数。以下のステートメントはそれをより明確に説明しています。
SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
MySQL Workbench を使用してテーブルを CSV 形式にエクスポートする
CSV ファイルをエクスポートするためにデータベース サーバーにアクセスしたくない場合は、MySQL が別の方法、つまり MySQL Workbench を使用する方法を提供します。 Workbench は、コマンドライン ツールを使用せずに MySQL データベースを操作するための GUI ツールです。これにより、ステートメントの結果セットをローカル システムの CSV 形式にエクスポートできます。これを行うには、以下の手順に従う必要があります。
- ステートメント/クエリを実行し、その結果セットを取得します。
- 次に、結果パネルで、 'レコードセットを外部ファイルにエクスポート' オプション。レコードセットは結果セットに使用されます。
- 最後に、新しいダイアログ ボックスが表示されます。ここでは、ファイル名とその形式を指定する必要があります。詳細を入力したら、 保存 ボタン。次の図は、それをより明確に説明しています。
ここで、指定したパスに移動して結果を確認できます。