95 lines
3.5 KiB
JavaScript
95 lines
3.5 KiB
JavaScript
require('dotenv').config();
|
|
const mysql = require('mysql2/promise');
|
|
|
|
/**
|
|
* 数据库初始化脚本
|
|
* 创建数据库和表结构
|
|
*/
|
|
async function initDatabase() {
|
|
const connection = await mysql.createConnection({
|
|
host: process.env.DB_HOST || 'localhost',
|
|
port: parseInt(process.env.DB_PORT || '3306'),
|
|
user: process.env.DB_USER || 'root',
|
|
password: process.env.DB_PASSWORD || '',
|
|
multipleStatements: true
|
|
});
|
|
|
|
try {
|
|
const dbName = process.env.DB_NAME || 'steel_prices';
|
|
|
|
// 创建数据库
|
|
console.log(`📦 正在创建数据库: ${dbName}`);
|
|
await connection.execute(
|
|
`CREATE DATABASE IF NOT EXISTS ${dbName} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`
|
|
);
|
|
console.log('✅ 数据库创建成功');
|
|
|
|
// 切换到目标数据库
|
|
await connection.changeUser({ database: dbName });
|
|
|
|
// 创建价格表
|
|
console.log('📋 正在创建价格表...');
|
|
const createTableSQL = `
|
|
CREATE TABLE IF NOT EXISTS prices (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
|
|
price_id VARCHAR(64) UNIQUE NOT NULL COMMENT '价格唯一ID',
|
|
goods_material VARCHAR(32) NOT NULL COMMENT '材质牌号',
|
|
goods_spec VARCHAR(16) NOT NULL COMMENT '规格型号',
|
|
partsname_name VARCHAR(32) NOT NULL COMMENT '品名',
|
|
productarea_name VARCHAR(64) NOT NULL COMMENT '产地/钢厂',
|
|
price_source VARCHAR(32) NOT NULL COMMENT '价格来源',
|
|
price_region VARCHAR(32) NOT NULL COMMENT '价格地区',
|
|
pntree_name VARCHAR(32) NOT NULL COMMENT '分类名称',
|
|
price_date DATETIME NOT NULL COMMENT '价格日期',
|
|
make_price INT DEFAULT NULL COMMENT '钢厂价（元/吨）',
|
|
hang_price INT NOT NULL COMMENT '挂牌价（元/吨）',
|
|
last_make_price INT DEFAULT NULL COMMENT '上次钢厂价',
|
|
last_hang_price INT DEFAULT NULL COMMENT '上次挂牌价',
|
|
make_price_updw VARCHAR(8) DEFAULT NULL COMMENT '钢厂价涨跌',
|
|
hang_price_updw VARCHAR(8) DEFAULT NULL COMMENT '挂牌价涨跌',
|
|
operator_code VARCHAR(16) DEFAULT NULL COMMENT '操作员代码',
|
|
operator_name VARCHAR(32) DEFAULT NULL COMMENT '操作员名称',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
INDEX idx_price_date (price_date),
|
|
INDEX idx_region_material (price_region, goods_material),
|
|
INDEX idx_source_date (price_source, price_date),
|
|
INDEX idx_goods_spec (goods_spec)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='钢材价格表'
|
|
`;
|
|
|
|
await connection.execute(createTableSQL);
|
|
console.log('✅ 价格表创建成功');
|
|
|
|
// 显示表结构信息
|
|
const [tables] = await connection.execute('SHOW TABLES');
|
|
console.log('\n📊 当前数据库表:');
|
|
tables.forEach(table => {
|
|
console.log(` - ${Object.values(table)[0]}`);
|
|
});
|
|
|
|
console.log('\n🎉 数据库初始化完成！');
|
|
|
|
} catch (error) {
|
|
console.error('❌ 数据库初始化失败:', error.message);
|
|
throw error;
|
|
} finally {
|
|
await connection.end();
|
|
}
|
|
}
|
|
|
|
// 如果直接运行此脚本
|
|
if (require.main === module) {
|
|
initDatabase()
|
|
.then(() => {
|
|
console.log('✅ 脚本执行完成');
|
|
process.exit(0);
|
|
})
|
|
.catch(err => {
|
|
console.error('❌ 脚本执行失败:', err);
|
|
process.exit(1);
|
|
});
|
|
}
|
|
|
|
module.exports = initDatabase;
|