使用较短的数据库查询代码将数据插入mysql db表

q3qa4bjr  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(233)

我有一些关于将数据插入mysql数据库的问题。实际上,我已经成功了。但是我认为代码太长了,所以当服务器出现问题或其他问题时,代码看起来很糟糕,很难修改。
这是我的代码,代码很长,所以我想知道,如果有一些解决方案,使它更短的代码。
结果变量有json数据,似乎我可以在db查询中使用它。我已经认为我可以做数组或对象,我尝试了一些代码,但他们不工作。

router.post("/user", function(req, res, next) {
  let id = req.body.id;
  let githubAPI = "https://api.github.com/users/";

  let options = {
    url: githubAPI + id,
    headers: {
      "User-Agent": "request"
    }
  };
  console.log(id);
  request(options, function(error, response, data) {
    if (error) {
      throw error;
    }
    // result have JSON Data
    let result = JSON.parse(data);

    let nick = result.login;
    let id = result.id;
    let node_id = result.node_id;
    let avatar_url = result.avatar_url;
    let gravatar_id = result.gravatar_id;
    let url = result.url;
    let html_url = result.html_url;
    let followers_url = result.followers_url;
    let following_url = result.following_url;
    let gists_url = result.gists_url;
    let starred_url = result.starred_url;
    let subscriptions_url = result.subscriptions_url;
    let organizations_url = result.organizations_url;
    let repos_url = result.repos_url;
    let events_url = result.events_url;
    let received_events_url = result.received_events_url;
    let type = result.type;
    let site_admin = result.site_admin;
    let name = result.name;
    let company = result.company;
    let blog = result.blog;
    let location = result.location;
    let email = result.email;
    let hireable = result.hireable;
    let bio = result.bio;
    let public_repos = result.public_repos;
    let public_gists = result.public_gists;
    let followers = result.followers;
    let following = result.following;
    let created_at = result.created_at;
    let updated_at = result.updated_at;

    if (bio == null) {
      bio = "Developer";
    }
    db.query(
      `INSERT INTO user (login, id, node_id, avatar_url, gravatar_id, url, html_url, followers_url, following_url, gists_url, starred_url, subscriptions_url, organizations_url, repos_url, events_url, received_events_url, type, site_admin, name, company, blog, location, email, hireable, bio, public_repos, public_gists, followers, following, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
      [
        nick,
        id,
        node_id,
        avatar_url,
        gravatar_id,
        url,
        html_url,
        followers_url,
        following_url,
        gists_url,
        starred_url,
        subscriptions_url,
        organizations_url,
        repos_url,
        events_url,
        received_events_url,
        type,
        site_admin,
        name,
        company,
        blog,
        location,
        email,
        hireable,
        bio,
        public_repos,
        public_gists,
        followers,
        following,
        created_at,
        updated_at
      ]
    );
  });
fcg9iug3

fcg9iug31#

不要硬编码sql和对象的翻译,只要保持字段的预期顺序为常量,然后将解析后的json中的值Map到 values .
也可以从相同的常量列表生成sql和占位符:

// Keep a list for the fields where order is important
const fieldOrder = [
  'login',
  'id',
  'node_id',
  'avatar_url',
  'gravatar_id',
  'url',
  'html_url',
  'followers_url',
  'following_url',
  'gists_url',
  'starred_url',
  'subscriptions_url',
  'organizations_url',
  'repos_url',
  'events_url',
  'received_events_url',
  'type',
  'site_admin',
  'name',
  'company',
  'blog',
  'location',
  'email',
  'hireable',
  'bio',
  'public_repos',
  'public_gists',
  'followers',
  'following',
  'created_at',
  'updated_at'
];

// Parse your content in the same place    
let result = JSON.parse(data);

// Extract the value by the same key names
let values = fieldOrder.map(k => result[k]);

// Generate the statement rather than hardcoding    
let sql = `INSERT into user (${fieldOrder.join(',')}) values(${fieldOrder.map(e => '?').join(',')})`

// pass these arguments to your function    
db.query(sql, values);

这相当简单,本质上是许多orm库在其功能实现的外观下为您所做的。
请注意,其中的许多部分是合理的通用性和可重用的,这是此类库实现的另一个特性。
因此,您无法真正避免的一个“邪恶”是保留字段列表,因为顺序可能很重要,这是清除任何意外数据的合理方法。
“便宜又讨厌”的方式可能是:

let fieldOrder = Object.keys(result);

甚至:

let [fieldOrder, values] = Object.entries(result);

但这并不能真正让您控制在 data ,也可能具有潜在的破坏性。
无论如何,即使在代码中的某个地方保持一个不变的列表,只要将列出相同字段名的所有位置移到一个列表中,就可以大大减少当前列表的数量。

相关问题