Knex使用原始字符串进行迁移

Question

我正在尝试使用原始 SQL 运行 knex 迁移来创建一个表，而不是在 knex 中编写。但出现了什么问题？我正在使用 es6 模板字符串跨越多行，但它没有迁移。

这里是我得到的错误：

错误：ER_PARSE_ERROR：您的 SQL 语法有误；请检查与您的 MySQL 服务器版本相对应的手册，以获取使用正确语法的方法，请参见第22行附近的语法：CREATE TABLE mg_customer_entity_varchar(value_id int(11) NOT NULL AU

这是我的代码：

require('babel-register')


exports.up = function(knex, Promise) {
  let raw = `
    CREATE TABLE \`mg_customer_entity\` (
      \`entity_id\` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
      \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
      \`attribute_set_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set Id',
      \`website_id\` smallint(5) unsigned DEFAULT NULL COMMENT 'Website Id',
      \`email\` varchar(255) DEFAULT NULL COMMENT 'Email',
      \`group_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
      \`increment_id\` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
      \`store_id\` smallint(5) unsigned DEFAULT '0' COMMENT 'Store Id',
      \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At',
      \`updated_at\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',
      \`is_active\` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
      \`disable_auto_group_change\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Disable automatic group change based on VAT ID',
      PRIMARY KEY (\`entity_id\`),
      UNIQUE KEY \`UNQ_MG_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_STORE_ID\` (\`store_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_ENTITY_TYPE_ID\` (\`entity_type_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_WEBSITE_ID\` (\`website_id\`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity';

    CREATE TABLE \`mg_customer_entity_varchar\` (
      \`value_id\` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value Id',
      \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
      \`attribute_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Id',
      \`entity_id\` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Id',
      \`value\` varchar(255) DEFAULT NULL COMMENT 'Value',
      PRIMARY KEY (\`value_id\`),
      UNIQUE KEY \`UNQ_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID\` (\`entity_id\`,\`attribute_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_TYPE_ID\` (\`entity_type_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID\` (\`attribute_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID\` (\`entity_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE\` (\`entity_id\`,\`attribute_id\`,\`value\`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity Varchar';
  `
  return knex.schema.raw(raw)
};

exports.down = function(knex, Promise) {
  return knex.schema
    .dropTableIfExists('mg_customer_entity')
    .dropTableIfExists('mg_customer_entity_varchar')
};

- ThomasReggi

Answer 1

1. 我没有正确使用babel-register
2. 必须将updated_at的默认值从'0000-00-00 00:00:00'更改为CURRENT_TIMESTAMP

migrations/20160908144316_beerhawkAuth.js

require('babel-register')
let value = require('../migrations-es6/20160908144316_beerhawkAuth.js')
exports.up = value.up
exports.down = value.down

migrations-es6/20160908144316_beerhawkAuth.js

export let up = function(knex, Promise) {
  let create_mg_customer_entity = `
  CREATE TABLE \`mg_customer_entity\` (
    \`entity_id\` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
    \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
    \`attribute_set_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set Id',
    \`website_id\` smallint(5) unsigned DEFAULT NULL COMMENT 'Website Id',
    \`email\` varchar(255) DEFAULT NULL COMMENT 'Email',
    \`group_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
    \`increment_id\` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
    \`store_id\` smallint(5) unsigned DEFAULT '0' COMMENT 'Store Id',
    \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At',
    \`updated_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Updated At',
    \`is_active\` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
    \`disable_auto_group_change\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Disable automatic group change based on VAT ID',
    PRIMARY KEY (\`entity_id\`),
    UNIQUE KEY \`UNQ_MG_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_STORE_ID\` (\`store_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_ENTITY_TYPE_ID\` (\`entity_type_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_WEBSITE_ID\` (\`website_id\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity';
  `
  let create_mg_customer_entity_varchar = `
  CREATE TABLE \`mg_customer_entity_varchar\` (
    \`value_id\` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value Id',
    \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
    \`attribute_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Id',
    \`entity_id\` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Id',
    \`value\` varchar(255) DEFAULT NULL COMMENT 'Value',
    PRIMARY KEY (\`value_id\`),
    UNIQUE KEY \`UNQ_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID\` (\`entity_id\`,\`attribute_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_TYPE_ID\` (\`entity_type_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID\` (\`attribute_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID\` (\`entity_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE\` (\`entity_id\`,\`attribute_id\`,\`value\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity Varchar';
  `
  return knex.schema
  .raw(create_mg_customer_entity)
  .raw(create_mg_customer_entity_varchar)
};

export let down = function(knex, Promise) {
  return knex.schema
    .dropTableIfExists('mg_customer_entity')
    .dropTableIfExists('mg_customer_entity_varchar')
};

- ThomasReggi