71 lines
2.5 KiB
JavaScript
71 lines
2.5 KiB
JavaScript
|
// 一次性迁移脚本：为 goods 表补充 goods_no、barcode、is_on_sale、cost_price、remark 等字段
|
||
|
|
// 用法：node add_goods_no_barcode_columns.js
|
||
|
|
|
||
|
|
const mysql = require('mysql2/promise')
|
||
|
|
require('dotenv').config()
|
||
|
|
|
||
|
|
const COLUMNS = [
|
||
|
|
{ name: 'cost_price', ddl: 'ALTER TABLE goods ADD COLUMN `cost_price` decimal(10,2) DEFAULT 0 COMMENT "成本价"' },
|
||
|
|
{ name: 'goods_no', ddl: 'ALTER TABLE goods ADD COLUMN `goods_no` varchar(64) DEFAULT "" COMMENT "商品货号"' },
|
||
|
|
{ name: 'barcode', ddl: 'ALTER TABLE goods ADD COLUMN `barcode` varchar(64) DEFAULT "" COMMENT "商品条码"' },
|
||
|
|
{ name: 'is_on_sale', ddl: 'ALTER TABLE goods ADD COLUMN `is_on_sale` tinyint(1) DEFAULT 1 COMMENT "是否上架 1-上架 0-下架"' },
|
||
|
|
{ name: 'remark', ddl: 'ALTER TABLE goods ADD COLUMN `remark` varchar(500) DEFAULT "" COMMENT "备注"' }
|
||
|
|
]
|
||
|
|
|
||
|
|
const INDEXES = [
|
||
|
|
{ name: 'idx_barcode', ddl: 'ALTER TABLE goods ADD INDEX `idx_barcode` (`barcode`)' },
|
||
|
|
{ name: 'idx_goods_no', ddl: 'ALTER TABLE goods ADD INDEX `idx_goods_no` (`goods_no`)' }
|
||
|
|
]
|
||
|
|
|
||
|
|
async function main() {
|
||
|
|
const config = {
|
||
|
|
host: process.env.DB_HOST || 'localhost',
|
||
|
|
port: parseInt(process.env.DB_PORT || '3306'),
|
||
|
|
user: process.env.DB_USER || 'root',
|
||
|
|
password: process.env.DB_PASSWORD || '',
|
||
|
|
database: process.env.DB_NAME || 'miniprogram'
|
||
|
|
}
|
||
|
|
|
||
|
|
const connection = await mysql.createConnection(config)
|
||
|
|
try {
|
||
|
|
const [columns] = await connection.execute('DESCRIBE goods')
|
||
|
|
const existing = new Set(columns.map(col => col.Field))
|
||
|
|
|
||
|
|
console.log('当前 goods 表字段:', [...existing].join(', '))
|
||
|
|
|
||
|
|
for (const col of COLUMNS) {
|
||
|
|
if (existing.has(col.name)) {
|
||
|
|
console.log(`- 字段 ${col.name} 已存在，跳过`)
|
||
|
|
continue
|
||
|
|
}
|
||
|
|
console.log(`- 添加字段 ${col.name} ...`)
|
||
|
|
await connection.execute(col.ddl)
|
||
|
|
}
|
||
|
|
|
||
|
|
const [indexes] = await connection.execute('SHOW INDEX FROM goods')
|
||
|
|
const existingIndexes = new Set(indexes.map(i => i.Key_name))
|
||
|
|
|
||
|
|
for (const idx of INDEXES) {
|
||
|
|
if (existingIndexes.has(idx.name)) {
|
||
|
|
console.log(`- 索引 ${idx.name} 已存在，跳过`)
|
||
|
|
continue
|
||
|
|
}
|
||
|
|
console.log(`- 添加索引 ${idx.name} ...`)
|
||
|
|
try {
|
||
|
|
await connection.execute(idx.ddl)
|
||
|
|
} catch (err) {
|
||
|
|
console.warn(` 索引 ${idx.name} 添加失败：${err.message}`)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
console.log('完成 ✅')
|
||
|
|
} finally {
|
||
|
|
await connection.end()
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
main().catch(err => {
|
||
|
|
console.error('迁移失败:', err)
|
||
|
|
process.exit(1)
|
||
|
|
})
|