mysql - 如何使用 mysql shell 将 json 转换为 sql

Question

我有一个 JSON 文件看起来像

{
  "msb": [
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-03-16T20:00:00+02:00",
  "dateFin": "2021-03-16T22:00:00+02:00",
  "meta": {
    "cree": "2021-03-10T16:22:36.223+01:00",
    "modofie": "2021-03-12T16:29:25.223+01:00"
  },
  "etat": "public",
  "titre": "MSB \"Orléans\" - 16 mars 2021",
  "url": "https://msb.fr/index.php/component/content/article/114-competitions/jeep-elite/5107-msb-lyon-villeurbanne-105-96-ap?Itemid=101",
  "nomOrganisation": "le-mans-sarthe-basket"
},
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-03-16T20:00:00+02:00",
  "dateFin": "2021-03-16T22:00:00+02:00",
   "meta": {
    "cree": "2021-03-10T16:22:36.223+01:00",
    "modofie": "2021-03-12T16:29:25.223+01:00"
  },
  "etat": "public",
  "titre": "MSB \"Boulazac\" - 06 avril 2021",
  "url": "https://msb.fr",
  "nomOrganisation": "le-mans-sarthe-basket"
},
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-04-06T20:00:00+02:00",
  "dateFin": "2021-04-06T22:00:00+02:00",
  "logo": {
    "logo": "logo_msb_180.png",
    "url": "https://msb.fr/images/teams/logo/logo_msb_180.png"
  },
  "meta": {
    "cree": "2021-03-10T16:22:36.223+01:00",
    "modofie": "2021-03-12T16:29:25.223+01:00"
  },
  "etat": "public",
  "titre": "MSB \"Orléans\" - 06 avril 2021",
  "url": "https://msb.fr",
  "nomOrganisation": "le-mans-sarthe-basket"
},
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-03-20T20:00:00+02:00",
  "dateFin": "2021-03-20T22:00:00+02:00",
   "meta": {
    "cree": "2021-03-15T16:22:36.223+01:00",
    "modofie": "2021-03-17T16:29:25.223+01:00"
  },
  "etat": "public",
  "titre": "MSB \"Orléans\" - 20 mars 2021",
  "url": "https://msb.fr",
  "nomOrganisation": "le-mans-sarthe-basket"
}
],
 "page_total": {
    "pageZ": 42,
    "Total_compte": 215,
    "pageI": 1,
    "pageT": 6,
    "numeroIdentifiant": "164784656259969"
  }
}

如何使用 mysql shell 在 SQL 中转换上述文件

我尝试了几个命令来导入我的文件：

创建表脚本：

CREATE DATABASE database_name;
CREATE TABLE joomla.table_name (
   doc JSON,
   id INTEGER AUTO_INCREMENT PRIMARY KEY 
) CHARSET utf8mb4 ENGINE=InnoDB;

连接到 mysqlx

mysqlsh --mysqlx -u root -h localhost -P 33060 -p
password : 1234

更改默认架构

shell.connect( {scheme:'database_name', user:'user', host:'localhost', port:33060} )

导入表table_name中的json文件的命令

util.importJson("/test.json", {schema: "database_name", table: "table_name", tableColumn: "doc"});

我们希望在我们的数据库中有以下结果

CREATE TABLE IF NOT EXISTS table_name(
    `msb_ville` VARCHAR(7) CHARACTER SET utf8,
    `msb_pays` VARCHAR(6) CHARACTER SET utf8,
    `msb_debutMatch` VARCHAR(25) CHARACTER SET utf8,
    `msb_dateFin` VARCHAR(25) CHARACTER SET utf8,
    `msb_meta_cree` VARCHAR(29) CHARACTER SET utf8,
    `msb_meta_modofie` VARCHAR(29) CHARACTER SET utf8,
    `msb_etat` VARCHAR(6) CHARACTER SET utf8,
    `msb_titre` VARCHAR(30) CHARACTER SET utf8,
    `msb_url` VARCHAR(126) CHARACTER SET utf8,
    `msb_nomOrganisation` VARCHAR(21) CHARACTER SET utf8,
    `msb_logo_logo` VARCHAR(16) CHARACTER SET utf8,
    `msb_logo_url` VARCHAR(49) CHARACTER SET utf8,
    `page_total_pageZ` INT,
    `page_total_Total_compte` INT,
    `page_total_pageI` INT,
    `page_total_pageT` INT,
    `page_total_numeroIdentifiant` BIGINT
);
INSERT INTO test VALUES
    ('Le Mans','FRANCE','2021-03-16T20:00:00+02:00','2021-03-16T22:00:00+02:00','2021-03-10T16:22:36.223+01:00','2021-03-12T16:29:25.223+01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-16T20:00:00+02:00','2021-03-16T22:00:00+02:00',NULL,NULL,'public','MSB "Orléans" - 16 mars 2021','https://msb.fr/index.php/component/content/article/114-competitions/jeep-elite/5107-msb-lyon-villeurbanne-105-96-ap?Itemid=101','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-16T20:00:00+02:00','2021-03-16T22:00:00+02:00','2021-03-10T16:22:36.223+01:00','2021-03-12T16:29:25.223+01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-16T20:00:00+02:00','2021-03-16T22:00:00+02:00',NULL,NULL,'public','MSB "Boulazac" - 06 avril 2021','https://msb.fr','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-04-06T20:00:00+02:00','2021-04-06T22:00:00+02:00',NULL,NULL,NULL,NULL,NULL,NULL,'logo_msb_180.png','https://msb.fr/images/teams/logo/logo_msb_180.png',NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-04-06T20:00:00+02:00','2021-04-06T22:00:00+02:00','2021-03-10T16:22:36.223+01:00','2021-03-12T16:29:25.223+01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-04-06T20:00:00+02:00','2021-04-06T22:00:00+02:00',NULL,NULL,'public','MSB "Orléans" - 06 avril 2021','https://msb.fr','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-20T20:00:00+02:00','2021-03-20T22:00:00+02:00','2021-03-15T16:22:36.223+01:00','2021-03-17T16:29:25.223+01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-20T20:00:00+02:00','2021-03-20T22:00:00+02:00',NULL,NULL,'public','MSB "Orléans" - 20 mars 2021','https://msb.fr','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,42,215,1,6,164784656259969);

标签： mysqlsqljsonmysql-shell