Node.js+Sequelize.js：MySQL订单表插入数据后设置时间可行吗？如何实现？

Absolutely, this is completely doable when using Sequelize.js with MySQL—you’ve got a few solid options depending on your exact workflow. Let’s walk through them step by step, tailored to your order table with start_time and close_time fields.

1. Option 1: Set Timestamps at Insert Time (or Leverage MySQL Defaults) #

If you want to set start_time the moment you insert the order (or let MySQL handle it automatically), you have two paths here:

a) Let MySQL Handle the Default Timestamp #

First, you can modify your MySQL table to set a default value for start_time (if you haven’t already):

ALTER TABLE orders MODIFY COLUMN start_time DATETIME DEFAULT CURRENT_TIMESTAMP;

Then, in your Sequelize model, you just need to define the field without requiring a value on insert:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('your_database', 'your_user', 'your_password', {
  host: 'localhost',
  dialect: 'mysql'
});

const Order = sequelize.define('Order', {
  // Add your other order fields here (e.g., orderNumber, customerId)
  startTime: {
    type: DataTypes.DATE,
    field: 'start_time', // Maps to MySQL's `start_time` column
    allowNull: false // Since MySQL sets the default, this is safe
  },
  closeTime: {
    type: DataTypes.DATE,
    field: 'close_time',
    allowNull: true // We'll set this later
  }
}, {
  tableName: 'orders',
  timestamps: false // Disable Sequelize's default createdAt/updatedAt if you don't need them
});

When you create an order, you don’t need to pass start_time—MySQL will fill it automatically:

async function createOrder() {
  const newOrder = await Order.create({
    // Only pass your other required fields here
    orderNumber: 'ORD-12345',
    customerId: 42
  });
  return newOrder;
}

b) Manually Set Timestamp on Insert via Sequelize #

If you prefer to control the timestamp directly in your code, use Sequelize.literal() to use MySQL’s CURRENT_TIMESTAMP function:

async function createOrderWithStartTime() {
  const newOrder = await Order.create({
    orderNumber: 'ORD-12345',
    customerId: 42,
    startTime: Sequelize.literal('CURRENT_TIMESTAMP')
  });
  return newOrder;
}

2. Option 2: Update Timestamps After Insertion #

If you need to set close_time (or even start_time) after the initial insert (e.g., when an order is completed), you can use Sequelize’s instance or bulk update methods.

a) Update via the Order Instance #

Once you have the order instance, call update() directly on it:

async function markOrderAsCompleted(orderId) {
  const order = await Order.findByPk(orderId);
  if (!order) throw new Error('Order not found');
  
  await order.update({
    closeTime: Sequelize.literal('CURRENT_TIMESTAMP')
  });
  
  return order;
}

b) Bulk Update (Without Fetching the Instance First) #

If you don’t need the order instance beforehand, you can use a direct update with a where clause:

async function setCloseTimeForOrder(orderId) {
  const [rowsUpdated] = await Order.update(
    { closeTime: Sequelize.literal('CURRENT_TIMESTAMP') },
    { where: { id: orderId } }
  );
  
  return rowsUpdated > 0; // Returns true if the order was updated
}

Key Notes #

• If you need to set both start_time and close_time right after inserting, you can combine the insert and update steps in a single transaction to ensure data consistency:

  async function createOrderAndSetBothTimes() {
    const transaction = await sequelize.transaction();
    try {
      const newOrder = await Order.create({
        orderNumber: 'ORD-12346',
        customerId: 43
      }, { transaction });
      
      await newOrder.update({
        startTime: Sequelize.literal('CURRENT_TIMESTAMP'),
        closeTime: Sequelize.literal('CURRENT_TIMESTAMP')
      }, { transaction });
      
      await transaction.commit();
      return newOrder;
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  }
  

• Always use Sequelize.literal('CURRENT_TIMESTAMP') instead of new Date() if you want the timestamp to come from the MySQL server (avoids timezone discrepancies between your app server and database).

内容的提问来源于stack exchange，提问作者Dinesh Kumar