MySQL导入导出实践 (2)

ConvertBookToEs.php

<?php /** * 转换wish_book为ES 批量格式(json) */ //id,create_time,update_time,price,title,intro function dealBook($file) { $fp = fopen($file, 'r'); while (!feof($fp)) { $line = explode(' @@@ ', fgets($fp, 65535)); if ($line && isset($line[1])) { $arr_head = [ 'index' => [ '_id' => (int)$line[0] ] ]; $arr = [ 'id' => (int)$line[0], 'create_time' => strtotime($line[1]), 'update_time' => strtotime($line[2]), 'price' => intval($line[3]), 'title' => (string)$line[4], 'intro' => (string)$line[18], ]; file_put_contents($file . '.json', json_encode($arr_head, JSON_UNESCAPED_UNICODE) . PHP_EOL, FILE_APPEND); file_put_contents($file . '.json', json_encode($arr, JSON_UNESCAPED_UNICODE) . PHP_EOL, FILE_APPEND); } } } try { //处理CSV文件为es bluk json格式 //参考 https://www.elastic.co/guide/en/elasticsearch/reference/current/_batch_processing.html $files = glob("/tmp/book_es.csv.*.csv"); if (false === $files) { exit("can not find csv file"); } $pids = []; foreach ($files as $i => $file) { $pid = pcntl_fork(); if ($pid < 0) { exit("could not fork"); } if ($pid > 0) { $pids[$pid] = $pid; } else { echo time() . " new process, pid:" . getmypid() . PHP_EOL; dealBook($file); exit(); } } while (count($pids)) { foreach ($pids as $key => $pid) { $res = pcntl_waitpid($pid, $status, WNOHANG); if ($res == -1 || $res > 0) { echo 'Child process exit,pid ' . $pid . PHP_EOL; unset($pids[$key]); } } sleep(1); } } catch (Exception $e) { $message = $e->getFile() . ':' . $e->getLine() . ' ' . $e->getMessage(); echo $message; } 参考

1、Linux命令行文本工具 - 飞鸿影~ - 博客园
https://www.cnblogs.com/52fhy/p/5836429.html
2、mysqldump 导出 csv 格式 --fields-terminated-by=, ：字段分割符； - superhosts的专栏 - CSDN博客
https://blog.csdn.net/superhosts/article/details/26054997
3、Batch Processing | Elasticsearch Reference [6.4] | Elastic
https://www.elastic.co/guide/en/elasticsearch/reference/current/_batch_processing.html
4、mysql导入数据load data infile用法整理 - conanwang - 博客园
https://www.cnblogs.com/conanwang/p/5890753.html
5、MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE - Stack Overflow
https://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update
6、mysql - INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE - Stack Overflow
https://stackoverflow.com/questions/2472229/insert-into-select-from-on-duplicate-key-update
7、MySQL :: MySQL 5.6参考手册:: 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE语法
https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
8、复制表结构和数据SQL语句 - becket - 博客园
https://www.cnblogs.com/zhengxu/articles/2206894.html