SQL INSERT CSV変換でNULLと空文字を区別する方法

公開ツールSQL INSERT CSV抽出器

目次
  1. 問題
  2. 解決
  3. よくつまずくポイント
  4. まとめ
  5. よくある質問

SQL の INSERT 文を CSV に移すとき、NULL と空文字列('')はどちらも同じ「空欄」に見えてしまいがちです。その結果、後から「どの値がもともと存在しなかったのか」「どの値がわざと空文字列として保存されていたのか」が区別できなくなる問題が起きます。この2つは SQL では明確に別の値ですが、ルールを決めずに移すと CSV の段階でその情報が失われます。以下では、手作業で区別するためのルールと、変換結果でもその区別が保たれているかを確認する方法をまとめます。

要点 ① SQL で引用符のない NULL と、引用符だけの空文字列 '' はもともと別の値です。 ② 移すときは NULL を文字そのままに残し、空文字列は本当の空欄として残すと、後から区別できます。 ③ 変換した CSV を読み込み直すプログラムの欠損値の扱い設定も合わせて確認する必要があります。

問題

会員テーブルを CSV に書き出すとします。ある会員は入会時にメール項目をまったく入力しなかったため値が NULL のまま残り、別の会員はアプリのロジック上、空文字列 '' として保存されていました。どちらも「メールがない」という点では似ていますが、データベースの立場では、一方は「値そのものが存在しない」、もう一方は「空文字列という値が存在する」で異なります。

CSV に移すときにこの区別をせず、どちらも空欄として処理してしまうと、その CSV を見ただけでは、どの会員が NULL で、どの会員が空文字列だったのかを知る方法がありません。元の SQL を開き直さない限り元に戻せない、情報の損失です。

特に、データを別のシステムに移すとき、テスト用のサンプルデータを作るとき、あるいは特定の値が空の行だけを絞り込むとき、この区別がないとフィルタリングの基準そのものを立てにくくなります。

解決

SQL の文法では、この2つの値は書き方からして違います。引用符なしで単に NULL と書かれていれば「値がない」という意味のキーワードであり、引用符2つだけの '' は長さ 0 の文字列という実際の値です。この違いを CSV に移すときもそのまま保てば、区別は消えません。

手作業で移す場合は、次の順序で進めます。

  1. VALUES の後ろの括弧内の値をカンマで区切ります(引用符の中のカンマは無視します)。
  2. 値が引用符なしで NULL とだけ書かれていれば、CSV の欄に NULL という文字をそのまま残します。
  3. 値が引用符2つだけなら('')、CSV の欄は本当に何の文字もない空欄にします。
  4. 移した結果で、カンマの数が元のカラム数より1つ少ないか数えてみます。途中で空欄が消えるとカンマの数が減ります。

なお、MySQL の LOAD DATA / SELECT ... INTO OUTFILE は、NULL を \N という別の表示として出力する方式をデフォルトで使います。NULL は空欄ではなく、目に見える別の表示として残すべきだというルールは、SQL の世界でもすでに使われているやり方です。

このルールどおりに変換されているか、当社の無料ツールで確認してみてください。

SQL INSERT CSV抽出器

SQL INSERT文を貼り付けるだけで、NULLと空文字を区別したCSVにすぐ変換します。

よくつまずくポイント

状況確認するポイント
INSERT INTO の後にカラムリストがないヘッダー行なしで値だけが変換されるため、何番目の欄がどの項目なのか分かりません
文字列の値がたまたま 'NULL'(引用符を含む)の場合引用符が外れて、本物の NULL と同じ文字 NULL に見えます
INSERT 文を複数まとめて貼り付けた最初の VALUES 以降のテキストを丸ごと括弧単位で読むため、次の文のカラムリストの括弧までデータ行として誤って読み込むことがあります
カンマ以外の区切り文字を選んだ値の中にその区切り文字と同じ文字があると、自動的に二重引用符で囲んで出力されます
変換した CSV を Excel・pandas などで読み込み直すときプログラムのデフォルト設定が、空欄と NULL の文字をどちらも欠損値としてまとめて認識することがあるため、読み込みオプションを別途確認する必要があります

まとめ

NULL と空文字列は SQL では別の値であり、CSV に移すときもこの違いをそのまま残さないと、後から区別できません。NULL は文字そのまま、空文字列は本当の空欄として残すというルールさえ守れば、手作業でも確認できますし、変換結果を読み込み直すプログラムの欠損値設定まで確認すれば、情報は最後まで保たれます。

SQL INSERT CSV抽出器

SQL INSERT文を貼り付けるだけで、NULLと空文字を区別したCSVにすぐ変換します。

よくある質問

NULL を空欄に変えたいときはどうすればいいですか?

このツールは、SQL に書かれた NULL という文字をそのまま CSV の欄に移す方式です。空欄に変えたい場合は、変換後の CSV を受け取ったあと、テキストエディタやスプレッドシートの検索・置換で NULL の文字だけを消してください。ただし、もともと引用符で囲まれた文字列 'NULL' とは見た目で区別がつかないので、紛らわしい行は元の SQL と照らし合わせることをおすすめします。

カラム名なしで保存された INSERT 文も変換できますか?

値だけが変換されます。ただしヘッダーがないと、何番目の欄がどのカラムなのか分からず、NULL と空文字列がどの項目から来た値なのか混乱しやすくなります。できれば元の INSERT 文にカラムリストを入れてから変換することをおすすめします。

INSERT 文を複数まとめて貼り付けても大丈夫ですか?

1つずつ変換することをおすすめします。複数の INSERT 文をつなげると、最初の VALUES 以降のテキストをすべて走査して括弧単位で値を読むため、2つ目の文のカラムリストの括弧までデータ行として誤って混ざり込むことがあります。

この記事は韓国語の原文をもとに作成しています。 韓国語の原文を読む

関連記事

  1. vCard(.vcf)とは?異なるアプリ間で連絡先が移せる理由vCard(.vcf)が特定の会社の技術ではなく公開されたテキスト標準であること、名前にセミコロンやカンマがあるとエスケープが必要な理由を解説します。
  2. XMLをJSONに変換する方法|APIが今もXMLを使う理由公共機関や旧バージョンのAPIが今もXMLで応答する背景(SOAP・韓国の電子政府標準フレームワークなど)と、XMLをJSONに変換して扱いやすくする方法をまとめます。
  3. CSVの予定表をカレンダーに一括登録|行が抜ける理由と対策複数の予定を.icsファイルにまとめてカレンダーへ一括登録する仕組みと、列名や日付形式が合わず行が通知なく抜け落ちる条件を、実際の動作に沿って解説します。
  4. CSVアップロード失敗は列の順序と名前を確認CSVを他のプログラムにアップロードしたときに値がずれる・拒否される原因を、列の順序とヘッダー名の照合方法から解説。目に見えない空白の点検方法もまとめました。
  5. CSV列順の並び替えで列数が違う行はどうなるかCSVの列順を並び替えると、行ごとの列数がヘッダーと違う場合に値がどこへ入りどこで切られるかを解説。並び替え前に列数を数式で確認する方法も紹介します。
  6. CSV重複削除の基準 行全体と特定の列で結果は変わる行全体が同じときだけ重複とみなすか、メールアドレスなど特定の列が同じなら重複とみなすかで、削除される行は変わります。基準を狭くした場合と広くした場合の失敗例を、表を使って説明します。

関連ツール