我正在使用一对PHP脚本。一个脚本从MYSQL数据库读取数据并将其导出到csv文件,然后第二个脚本使用csv将导出的csv文件上载到另一个MySQL数据库实例。数据库表A(导出)和B(导入)的结构相同。
这些脚本适用于“常规” MySQL表和列类型。但是,当我们将它们应用于在其中一列中存储JSON对象的MySQL表(MySQL列类型为“ json”)时,导入将失败。
导出数据的脚本按预期工作,生成了一个CSV文件,其中JSON对象用双引号引起了...就像行中的其他值一样。
导出的CSV文件中的行如下所示(最后一项是一个复杂的json对象,为简单起见,缩写为):
"894","Somebody","Related","2020-02-20","{"name1":"value1","name2":"value2","name3":"value3"}","expired"
在PHP脚本中,导出数据本质上是这样的:
$rowStr = "894","Somebody","Related","2020-02-20","{"name1":"value1","name2":"value2","name3":"value3"}","expired";
file_put_contents($filepath, trim($rowStr), FILE_APPEND);
出口没有问题。按预期(与上述格式相同),该行出现在CSV文件中。
我将csv读入另一个数据库的代码如下所示:
$allRows = array_map('str_getcsv',file($fp)); // read the exported csv file where $fp is the path to the file
foreach($allRows as $i => $row) {
//get the col_names from the 2nd database table (identical to the first) where $ac-> is the class that handles data queries
$col_names = $ac->get_table_column_names('databasename',$tablename);
$update_arr = array();
foreach($col_names as $i => $cname) {
$update_arr[$cname['COLUMN_NAME']] = $val;
}
//and write the row to the 2nd db's table
$ac->create_update_table($update_arr,$tablename,FALSE);
}
并且,如果重要的话,下面是“ get_table_column_names”和“ create_update_table”函数中使用的查询:
get_table_column_names //使用PDO
SELECT COLUMN_NAME,COLUMN_DEFAULT,DATA_TYPE FROM information_schema.columns WHERE table_schema = :db AND table_name = :table
create_update_table
INSERT INTO 'tablename' (field1, field2, field3, field4,json_object_column) VALUES ("894","Somebody","Related","2020-02-20","{"name1":"value1","name2":"value2","name3":"value3"}")
问题在于,在导入时,该行将转换为如下所示的数组:
array (
[0] = "894",
[1] = "Somebody",
[2] = "Related",
[3] = "2020-02-20",
[4] = "{name1":"value1",
[5] = "name2:"value2", //should be part of node 4
[6] = "name3:"value3"}", //should be part of node 4
[7] = "expired"
);
发生的是,JSON对象中的“,”被视为字段分隔符,并且JSON被分解为数组节点。除了编写脚本来检测以“ {开始,以}结尾”的字段之外,我如何才能将整个json字符串作为一个字段读取(就像在数据库中一样)?或者,也许还有更好的方法来输出字符串,以便可以将其作为一个项目读取?
如果不是像file_put_contents()
使用诸如为CSV文件设计的某些方法那样仅使用某种方式写出数据,这将为您完成大部分工作...
写入数据使用fputcsv()
,这转义了定界符(在这种情况下,“变为””)...
$row = ["894","Somebody","Related","2020-02-20",'{"name1":"value1","name2":"value2","name3":"value3"}',"expired"];
$fh = fopen($filepath, "a");
fputcsv($fh, $row);
fclose($fh);
它将写入文件
894,Somebody,Related,2020-02-20,"{""name1"":""value1"",""name2"":""value2"",""name3"":""value3""}",expired
然后从文件中读取,一次只读取一行并使用fgetcsv()
...
$fh = fopen($filepath, "r");
print_r(fgetcsv($fh)); // This in a loop to read all lines
fclose($fh);
这表明
Array
(
[0] => 894
[1] => Somebody
[2] => Related
[3] => 2020-02-20
[4] => {"name1":"value1","name2":"value2","name3":"value3"}
[5] => expired
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句