csvファイルをMySQLへ読み込んだら、空白のところが勝手に0に置き換わる理由とリスク、データ型が変わることで影響するPHPの書き方
- csvファイルをMySQLへ読み込んだら、空白のところが勝手に0に置き換わる理由とリスク、データ型が変わることで影響するPHPの書き方について調査しました。
- データ型を決める際、数字はINT、文字列はVARCHARという認識が一般的ですが、INT型のカラムをVARCHAR型に変えるリスクとしては、データの不整合やパフォーマンスの低下が考えられます。
- DBのデータ型が「INT」と「VARCHAR」では、PHP側の書き方も変わってきます。
- ベストアンサー
csvをDBへ読み込んだら、NULLが勝手に0に
csvファイルをMySQLへ読み込んだら、空白のところが勝手に0に置き換わってしまいました。 置き換わったカラムの型が、全部INTだったので、 試しにVARCHARへ変え、再度、取り込み処理を実施してみたところ、 希望通りに(空白のところは空白のままで)無事データを取り込むことが出来ました。 ■質問1 考えられる原因としては、DBの設定？ それとも、PHP側の書き方でしょうか？ ■質問2 データ型を決める際、数字はINT、文字列はVARCHAR、ぐらいの認識しかないのですが、 今回のように、INT型のカラムを、VARCHAR型に、変えたりすると、 起こり得るリスクとしては、どのようなものが考えられでしょうか？ ■質問3 DBのデータ型が、「INT」と「VARCHAR」では、PHP側の書き方も変わってくるのでしょうか？ □PHPの内容 ▽まず、UTF-8形式で上書き exec("nkf -w --overwrite data.csv"); ▽接続後読み込み $sql = "LOAD DATA LOCAL INFILE '$file' INTO TABLE `読み込むテーブルの名前` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' LINES TERMINATED BY '\\r\\n'";
- re999
- お礼率61% (476/777)
- PHP
- 回答数2
- ありがとう数2
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
>考えられる原因としては、DBの設定？ >それとも、PHP側の書き方でしょうか？ 入力するcsvファイルの作成の仕方に、問題があります。 NULLで格納するには、入力ファイルの該当カラム位置に、\Nを入れておく必要があります。 例えば、次のような入力ファイルだったとします。 xxx,,yyyyy xxxxx,100,y xx,\N,yy 1レコード目の2番目のフィールドは値が入っていませんが、この場合の扱いは次のようになります。 列のデータ型が文字・・・長さ0の文字。すなわちnullではない。 列のデータ型が数値・・・0。すなわちnullではない。 >今回のように、INT型のカラムを、VARCHAR型に、変えたりすると、 >起こり得るリスクとしては、どのようなものが考えられでしょうか？ 数値以外のデータが入る可能性がありますが、それが実害が出るかどうかは、その列がどういう意味のものか次第です。 (1)在庫数など数値演算するような列 (2)商品コード、注文番号、フラグなどの列 といったことで話は違ってきます。
その他の回答 (1)
- yambejp
- ベストアンサー率51% (3827/7415)
空文字はNULLではありません 区切り記号を「;」、囲み記号を「"」として NULL値がOKでデフォルトがNULLのINT型フィールドにデータ投入するとき ""は0ですね。どうしてもNULLを投入したいならNULLと書いてください。 たとえば・・・ "100";"1000" "200";"" "300";"0" "400";NULL 200と300の行はともに0です。 処理としてはエディタかなにかで""をNULLに一括変換してから 投入すればいいでしょう。
お礼
回答ありがとうございました。 ＞空文字はNULLではありません ＞囲み記号を「"」としてNULL値がOKでデフォルトがNULLのINT型フィールドにデータ投入するとき""は0ですね。 全然知りませんでした。 勉強になりましたー
関連するQ&A
- exec関数の中に変数を入れたい
CSVファイル(kabu.csv)をmysqlに記録すべく下記のPHPを組みました。 PHPのexec関数の中で、MySQLを動作させます。 下記でPHPファイルと同じ階層にあるkabu.csvをMySQLにインサートすることが可能でした。 <?php try { $con = new PDO("mysql:host=127.0.0.1;dbname=db", "root", "password", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1)); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $con->exec('load data local infile \'kabu.csv\' IGNORE INTO TABLE photo_in fields terminated by \',\' enclosed by \'"\' escaped by \'"\''); } catch (PDOException $e) { echo $e->getMessage(); } ?> ********************************************************************************* 次に、 \'kabu.csv\'を外に出しました。($file = 'kabu.csv';) (将来的にこのkabu.csvはクエリーと連動して名前をが変わるようにする予定です。) ********************************************************************************** <?php $file = 'kabu.csv'; try { $con = new PDO("mysql:host=127.0.0.1;dbname=db", "root", "password", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1)); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $con->exec('LOAD DATA LOCAL INFILE $file IGNORE INTO TABLE photo_in fields terminated by \',\' enclosed by \'"\' escaped by \'"\''); } catch (PDOException $e) { echo $e->getMessage(); } ?> 単純に \'kabu.csv\'を$fileに置き換えただけではうまく動作しません。(MySQLに入力されません) どのように記載すれば、作動するでしょうか? 初歩的な質問かと思いますが、よろしくお願いいたします。 環境はCentOS 6.6 + MySQL5.6 です。エラーは下記となります。 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$file IGNORE INTO TABLE photo_in fields terminated by ',' enclosed by '"' escape' at line 1
- 締切済み
- PHP
- BAT処理でCSVデータのインポート
よろしくお願いしますm(__)m 昨日から調べて作ってみたのですがうまく動かず、 MYSQLが強制終了してしまうこともありました(T_T) 疲れました・・・ ※test.batに以下の内容を書きました c:\mysql\bin\mysql -u **** -p**** < test.sql ※test.sqlに以下の内容を書きました LOAD DATA INFILE "c:\mysql\test.csv" INTO TABLE 商品管理.商品単品 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "" ESCAPED BY "\\" LINES STARTING BY "" TERMINATED BY "\n" ※test.csvはカンマ区切りになっています。 ファイルは【test.bat】【test.sql】【test.csv】とも c:\mysqlに置いています。 この状態でtest.batを実行するとDOS窓が一瞬現れて消えて しまいます。 間違っているところやヒントなどあれば教えてくださいm(__)m
- ベストアンサー
- MySQL
- PHP+MySQL でのcsvファイルインポート
PHP + MySQL で、他システムから出力されてきたcsvファイルをテーブルへインポートしたいと考えています。 LOAD DATA INFILE で取り込もうとしていますが、対象のcsvファイルの特徴のためにうまく行かず苦戦しています。 取り込みたいファイルは、以下のようになっています。 ・各フィールドが "(ダブルクォート) で囲まれている ・各フィールドは ,(カンマ) で区切られている ・金額の項目には、桁区切りのカンマが入っている 例) "0001","あああああ",…(中略)…,"105,000","100,000","5,000",… コード 項目名 税込み 税抜き 消費税 というような形です。 困っているのは、金額に使われている、桁区切りのカンマの処理についてです。 目的としては、データベースへ取り込むときには 105,000 は 105000 として取り込めればいいのですが、他システムから、桁区切りがなされた状態でcsvが作成されてきています。 LOAD DATA INFILE test.csv INTO TABLE data_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES; 各項目のダブルクォートはENCLOSED BY で、項目を分けるカンマはTERMINATED BYで処理できていると思うのですが、金額の区切りに使われているカンマと項目の区切りのカンマを区別させられずに苦戦しています。 事前にcsvを加工するのも考えてはいるのですが、なるべくcsvを加工せずにそのまま取込したいと思います。 何か、簡単に回避できる方法等があればご教示願います。 宜しくお願いします。
- ベストアンサー
- PHP
- pathを指定してCSVをインポートしたい
すいません1つ質問があります。 こちらのサイトでCSVのファイルのインポート方法を勉強していますが、下記の方法だとあらかじめ、データベースが格納されているフォルダにCSVを置いてある前提でこのやり方をやります。 LOAD DATA INFILE "T01Prefecture.csv" INTO TABLE T01Prefecture FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n"; 僕が知りたいのは、例えば、 C:\Users\Ken\Desktop\T01Prefecture.csv ここに置いてあるCSVを指定してインポートしたいと考えています。 そのやり方がどうもこのパスを書くだけでは出来ないです。 どなたかやり方を教えて頂けますか？ 僕が書いたのは下記の通りです。 LOAD DATA INFILE "C:\Users\Ken\Desktop\T01Prefecture.csv" INTO TABLE T01Prefecture FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n"; よろしくお願いいたします。
- ベストアンサー
- MySQL
- SQL*LOADER 最終列のLENが１
お世話になります。 前提として 次のようなctlファイルがあります。 OPTIONS(ROWS=500,BINDSIZE=700000) LOAD DATA INFILE '/home/oracle/TEST/00_BIN/YOSAN.csv' TRUNCATE INTO TABLE MT_YOSAN ( 日付 TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', 店舗コード TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', 予算 TERMINATED BY ',' NULLIF 昨年実績=BLANKS, 昨年実績 NULLIF 昨年実績=BLANKS ) ロード対象のCSVですが、次のようなROWデータになります。 "2011/02/22","01010",30000,25000 テーブル仕様は次のようになっています。 日付 VARCHAR2(20) NOT NULL 店舗コード VARCHAR2(20) NOT NULL 予算 NUMBER(10) 昨年実績 NUMBER(10) これを実行した際に昨年実績のカラムに25000の先頭の2しかロードされません。 ログファイルを開くと他のLenは*に対して最後の昨年実績列は１が設定されていました。 この事象の原因と対処法がわかる方がいればご教示いただきたく存じます。 よろしくお願いいたします。 またsqlldr実行時は引数controlとlogのみ指定しています。
- ベストアンサー
- Oracle
- CSVファイルを毎日、全レコード一括更新したい
毎日、CSV形式で送られてくる最新のデータがあります。 それを自動的にMySQLに一括で追加・更新したいと考えています。 CSVファイルは毎日、データが追加されたり、UPDATEされたり、削除されたりしています。そのCSVファイルのデータを神様DBとして、上書きしたいと思っています。 つまり、前日のMySQLのデータを本日のデータで完全に上書きしたいと考えています。 単にデータをロードするだけなら mysql> load data infile "ファイルパス/ファイル名.csv" -> into table テーブル名 fields terminated by "," lines terminated by "\n"; でよいと思いますが、完全上書きの方法がよくわかりません。 それとも、上記方法で同じことを２度すると完全に上書きされるのでしょうか？同じレコードが２つ出来上がるのでしょうか？それともマージされるのでしょうか？ もし、お分かりでしたらご教授ください。
- ベストアンサー
- MySQL
- MySQL Date型にNULLが設定できない。
csvファイルからMySQLにデータを取り込む際に、Date型やint型にNULLを設定するにはどうしたらよいでしょうか。 以下のようなテーブルに | tTest | CREATE TABLE `ttest` ( `ID` bigint(20) NOT NULL, `title` text NOT NULL, `naiyou` varchar(100) DEFAULT NULL, `kaishi` date DEFAULT NULL, `owari` date DEFAULT NULL, `peirod` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=sjis | 以下のようなcsvファイルを読み込んだとき(*)、 10,テスト,2008-04-05,2009-04-23,43 11,テスト,2008-04-05,NULL,43 12,テスト,2008-04-05,2009-04-23,NULL ２行目のNULLで ERROR 1292 (22007): Incorrect date value: 'NULL' for column .... とエラーが発生します。 ３行目のNULLでもint型ですが同様にエラーとなります。 一方、insertで行の追加以下のようにした場合は成功します。 insert into tTest values (12,テスト,'2008-04-05',NULL,NULL) 外部ファイルから読み込んだ場合にNULL値を設定するにはどのようにしたらよいのでしょうか。 (*)以下のコマンド LOAD DATA INFILE "E:\\work\\MySQL\\sample.csv" INTO TABLE tTest FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n";
- ベストアンサー
- MySQL
- MySQLでCSV出力したい
MySQL初心者です。 テーブルから抽出したデータを CSVにてエクスポートしたいです。 SELECT * FROM tbl_employee INTO OUTFILE "/ttt.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; これだとフィールド名がエクスポートされないという現象が起こります。 また、エクスポート先なのですが、 デスクトップとかにしたいのですが、 設定の仕方がよくわかりません。 ネットで調べたのですが、限界でした。 まだまだ初心者なもので よろしくお願いいたします
- 締切済み
- MySQL
- LOAD DATA LOCAL INFILE
LOAD DATA LOCAL INFILE を使ってmysql にcsv からデータを挿入したいのですが #1148 - The used command is not allowed with this MySQL version とエラーが出てきます。※エラー情報はmyphpadminから得ています。 以下の文を実行しています。 ※他のサーバーで動いているので、ソースに間違いはないと思います。 ※サーバーの引っ越しで出てきた不具合ですが、以前何かをしたかは覚えていません。。。 LOAD DATA LOCAL INFILE '/パス/data.txt' REPLACE INTO TABLE data fields terminated by ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' 権限を付与すれば良い、と言う記事を読んだのですが 具体的にはどのような事をしたらいいのでしょうか？ FTPでファイルをアップロードして、php を実行しています。 MySQL 5.1.73 PHP 5.3.3 よろしくお願いいたします。
- 締切済み
- PHP
- データベース単位でのCSV出力方法について
データベース単位でのCSV出力方法について phpからデータベースのバックアップ機能として、 データベース単位でCSV出力を行いたいと思っています。 テーブル単位で出来る記事は見受けられるのですが、 上記の方法は可能でしょうか？ それともう一つお聞きしたいことがあります。 SQL文が格納された変数をexec関数で実行しているのですが、 直接phpmyadminでは成功するのにもかかわらず、 csvファイルが生成されません。 下記にSQL文を記しておきます。 --SQL文-- select * from users into outfile "C:/works/～中略～/dump_0426.csv" fields terminated by ',' ENCLOSED BY '"' ご教授よろしくお願い致します。 【環境】 DB:mysql5.0.51b-community PHP:5.2.6
- ベストアンサー
- MySQL
お礼
回答ありがとうございました。 ＞列のデータ型が文字・・・長さ0の文字。すなわちnullではない。 ＞列のデータ型が数値・・・0。すなわちnullではない。 など、説明、大変分かりやすかったです。 ＞実害が出るかどうかは、その列がどういう意味のものか次第 ＞(1)在庫数など数値演算するような列 ＞(2)商品コード、注文番号、フラグなどの列 こちらもありがとうございました。 言われてみれば、確かにそのとおりですね。 おかげで頭の中を整理することができましたー。