江苏龙网

  1. 主页 > 生活百科 > >

MySQL 8.0 shell util 特性( 二 )

MySQL 8.0


MySQL 127.0.0.1:3306 ssl ytt Py > sql create table tl1_json( id bigint primary key, content json);Query OK, 0 rows affected (0.3093 sec)重新定义文件以及导入选项 。
MySQL 127.0.0.1:3306 ssl ytt Py > y_file2='/var/lib/mysql-files/tl1.json'MySQL 127.0.0.1:3306 ssl ytt Py > rows=['content']MySQL 127.0.0.1:3306 ssl ytt Py > y_options2={"schema":"ytt","table":"tl1_json","dialect":"json","showProgress":True,"threads":4,'columns':rows}导入 JSON 数据
MySQL 127.0.0.1:3306 ssl ytt Py > util.import_table(y_file2,y_options2)Importing from file '/var/lib/mysql-files/tl1.json' to table `ytt`.`tl1_json` in MySQL Server at 127.0.0.1:3306 using 2 threads[Worker000] ytt.tl1_json: Records: 464633 Deleted: 0 Skipped: 0 Warnings: 0[Worker001] ytt.tl1_json: Records: 583943 Deleted: 0 Skipped: 0 Warnings: 0100% (90.15 MB / 90.15 MB), 2.71 MB/sFile '/var/lib/mysql-files/tl1.json' (90.15 MB) was imported in 23.3530 sec at 3.86 MB/sTotal rows affected in ytt.tl1_json: Records: 1048576 Deleted: 0 Skipped: 0 Warnings: 0速度也还可以，不到 24 秒 。
那导入 json 数据，就必须得提到以 X 插件协议通信的工具 import_json了 。
2. imort_json
我们切换到 mysqlx 端口
MySQL 127.0.0.1:3306 ssl ytt Py > c admin@127.0.0.1:33060Creating a session to 'admin@127.0.0.1:33060'Fetching schema names for autocompletion... Press ^C to stop.Closing old connection...Your MySQL connection id is 16 (X protocol)Server version: 8.0.17 MySQL Community Server - GPLNo default schema selected; type \use <schema> to set one. MySQL 127.0.0.1:33060+ ssl Py > \use yttDefault schema `ytt` accessible through db.-- 清空表tl1_json MySQL 127.0.0.1:33060+ ssl ytt Py > sql truncate tl1_json;Query OK, 0 rows affected (0.1098 sec)import_json 参数和 Import_table 参数类似，
这里我改下选项
MySQL 127.0.0.1:33060+ ssl ytt Py > y_file3=y_file2MySQL 127.0.0.1:33060+ ssl ytt Py > y_options3={"schema":"ytt","table":"tl1_json",'tableColumn':'content'}MySQL 127.0.0.1:33060+ ssl ytt Py > util.import_json(y_file3,y_options3)Importing from file "/var/lib/mysql-files/tl1.json" to table `ytt`.`tl1_json` in MySQL Server at 127.0.0.1:33060.. 517776.. 1032724.. 1048576.. 1048576Processed 90.15 MB in 1048576 documents in 35.2400 sec (29.76K documents/s)Total successfully imported documents 1048576 (29.76K documents/s)我在手册上没有看到多线程的选项，所以单线程跑 35 秒慢了些 。
查看刚刚导入的数据
MySQL 127.0.0.1:33060+ ssl ytt Py > sql select id,json_pretty(content) from tl1_json limit 1G*************************** 1. row *************************** id: 1json_pretty(content): { "id": 1, "r1": 10, "r2": 10, "r3": "mysql", "r4": "2019-09-16 16:49:50.000000"}1 row in set (0.0007 sec)import_json 不仅仅可以导入 Json 数据，更重要的是可以在 BSON 和 JSON 之间平滑的转换，有兴趣的同学可以去 TRY 下 。




推荐阅读


上一篇：古代最美男子潘安 中国古代第一美男子潘安

下一篇：nginx反向代理机制解决前端跨域问题