试图在mysql中存储特定的json键时出错

Question

我试图为存储在变量filtrado中的api存储特定的mysql密钥。

console.log(filtrado);

[
  {
    "id": 5,
    "name": "Chelsey Dietrich",
    "username": "Kamren",
    "email": "Lucio_Hettinger@annie.ca",
    "address": {
      "street": "Skiles Walks",
      "suite": "Suite 351",
      "city": "Roscoeview",
      "zipcode": "33263",
      "geo": {
        "lat": "-31.8129",
        "lng": "62.5342"
      }
    },
    "phone": "(254)954-1289",
    "website": "demarco.info",
    "company": {
      "name": "Keebler LLC",
      "catchPhrase": "User-centric fault-tolerant solution",
      "bs": "revolutionize end-to-end systems"
    }
  },
  {
    "id": 10,
    "name": "Clementina DuBuque",
    "username": "Moriah.Stanton",
    "email": "Rey.Padberg@karina.biz",
    "address": {
      "street": "Kattie Turnpike",
      "suite": "Suite 198",
      "city": "Lebsackbury",
      "zipcode": "31428-2261",
      "geo": {
        "lat": "-38.2386",
        "lng": "57.2232"
      }
    },
    "phone": "024-648-3804",
    "website": "ambrose.net",
    "company": {
      "name": "Hoeger LLC",
      "catchPhrase": "Centralized empowering task-force",
      "bs": "target end-to-end models"
    }
  },
  {
    "id": 3,
    "name": "Clementine Bauch",
    "username": "Samantha",
    "email": "Nathan@yesenia.net",
    "address": {
      "street": "Douglas Extension",
      "suite": "Suite 847",
      "city": "McKenziehaven",
      "zipcode": "59590-4157",
      "geo": {
        "lat": "-68.6102",
        "lng": "-47.0653"
      }
    },
    "phone": "1-463-123-4447",
    "website": "ramiro.info",
    "company": {
      "name": "Romaguera-Jacobson",
      "catchPhrase": "Face to face bifurcated interface",
      "bs": "e-enable strategic applications"
    }
  }
]

我只需要存储名字，电子邮件，用户名，我的index.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

  con.query("INSERT INTO users (`name`, `email`, `username`) FILTRADO ('?', '?', '?');", 
  filtrado, function (error, results, fields) {
    if (error) throw error;
    res.end(JSON.stringify(results));
  });

错误：

sqlMessage：You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FILTRADO (''[\\n {\\n \\"id\\": 5,\\n \\"name\\": \\"Chelsey Dietrich\\",\\n ...' at line 1，

Answer 1

没有正确插入数据，sql insert语法是错误的，您需要为查询创建一个嵌套数组数组，例如用于多个insert的insert数组。learn more here

按以下方式创建插入数据数组

[
    [ 'Chelsey Dietrich', 'Lucio_Hettinger@annie.ca', 'Kamren' ],
    [ 'Clementina DuBuque', 'Rey.Padberg@karina.biz', 'Moriah.Stanton' ],
    [ 'Clementine Bauch', 'Nathan@yesenia.net', 'Samantha' ]
]

这是解决你的问题的办法，试试这个。

let arr = filtrado

let sql = "INSERT INTO users (`name`, `email`, `username`) VALUES ?";

let insData = filtrado.map(item=>{  return [item.name, item.email, item.username]  });

con.query(sql, insData, function (error, results, fields) {
    if (error) throw error;
    res.end(JSON.stringify(results));
});