const mysql = require('mysql2/promise');
|
|
async function fixWxLoginTable() {
|
let connection;
|
|
try {
|
// 创建数据库连接
|
connection = await mysql.createConnection({
|
host: '139.155.104.10',
|
port: 3306,
|
user: 'ryc',
|
password: 'KiYap3E8X8RLcM6T',
|
database: 'ryc'
|
});
|
|
console.log('✅ 数据库连接成功');
|
|
// 检查表是否存在
|
const [tables] = await connection.execute(
|
"SHOW TABLES LIKE 't_wx_login_record'"
|
);
|
|
if (tables.length === 0) {
|
console.log('❌ 表 t_wx_login_record 不存在');
|
return;
|
}
|
|
console.log('✅ 表 t_wx_login_record 存在');
|
|
// 检查当前表结构
|
const [columns] = await connection.execute(
|
"DESCRIBE t_wx_login_record"
|
);
|
|
console.log('当前表结构:');
|
columns.forEach(col => {
|
console.log(` - ${col.Field}: ${col.Type} ${col.Null === 'YES' ? 'NULL' : 'NOT NULL'} ${col.Default ? `DEFAULT ${col.Default}` : ''}`);
|
});
|
|
// 检查需要添加的字段
|
const existingColumns = columns.map(col => col.Field);
|
const fieldsToAdd = [];
|
|
if (!existingColumns.includes('create_user_id')) {
|
fieldsToAdd.push({
|
name: 'create_user_id',
|
sql: 'ADD COLUMN create_user_id BIGINT NULL COMMENT \'创建用户ID\''
|
});
|
}
|
|
if (!existingColumns.includes('update_user_id')) {
|
fieldsToAdd.push({
|
name: 'update_user_id',
|
sql: 'ADD COLUMN update_user_id BIGINT NULL COMMENT \'更新用户ID\''
|
});
|
}
|
|
if (!existingColumns.includes('version')) {
|
fieldsToAdd.push({
|
name: 'version',
|
sql: 'ADD COLUMN version BIGINT NOT NULL DEFAULT 0 COMMENT \'版本号\''
|
});
|
}
|
|
if (fieldsToAdd.length === 0) {
|
console.log('✅ 所有必需字段都已存在，无需修改');
|
return;
|
}
|
|
console.log(`需要添加 ${fieldsToAdd.length} 个字段:`);
|
fieldsToAdd.forEach(field => {
|
console.log(` - ${field.name}`);
|
});
|
|
// 添加字段
|
for (const field of fieldsToAdd) {
|
console.log(`正在添加字段: ${field.name}...`);
|
|
try {
|
await connection.execute(`ALTER TABLE t_wx_login_record ${field.sql}`);
|
console.log(`✅ 成功添加字段: ${field.name}`);
|
} catch (error) {
|
console.error(`❌ 添加字段 ${field.name} 失败:`, error.message);
|
throw error;
|
}
|
}
|
|
// 添加索引
|
console.log('正在添加索引...');
|
|
try {
|
// 检查索引是否已存在
|
const [indexes] = await connection.execute(
|
"SHOW INDEX FROM t_wx_login_record WHERE Key_name = 'idx_create_user_id'"
|
);
|
|
if (indexes.length === 0) {
|
await connection.execute(
|
'CREATE INDEX idx_create_user_id ON t_wx_login_record(create_user_id)'
|
);
|
console.log('✅ 成功添加 create_user_id 索引');
|
} else {
|
console.log('✅ create_user_id 索引已存在');
|
}
|
} catch (error) {
|
console.log('⚠️ 添加 create_user_id 索引失败:', error.message);
|
}
|
|
try {
|
const [indexes] = await connection.execute(
|
"SHOW INDEX FROM t_wx_login_record WHERE Key_name = 'idx_update_user_id'"
|
);
|
|
if (indexes.length === 0) {
|
await connection.execute(
|
'CREATE INDEX idx_update_user_id ON t_wx_login_record(update_user_id)'
|
);
|
console.log('✅ 成功添加 update_user_id 索引');
|
} else {
|
console.log('✅ update_user_id 索引已存在');
|
}
|
} catch (error) {
|
console.log('⚠️ 添加 update_user_id 索引失败:', error.message);
|
}
|
|
// 验证修改结果
|
console.log('验证修改结果...');
|
const [newColumns] = await connection.execute(
|
"DESCRIBE t_wx_login_record"
|
);
|
|
console.log('修改后的表结构:');
|
newColumns.forEach(col => {
|
console.log(` - ${col.Field}: ${col.Type} ${col.Null === 'YES' ? 'NULL' : 'NOT NULL'} ${col.Default !== null ? `DEFAULT ${col.Default}` : ''}`);
|
});
|
|
console.log('✅ 表结构修复完成！');
|
|
} catch (error) {
|
console.error('❌ 修复表结构时发生错误:', error);
|
throw error;
|
} finally {
|
if (connection) {
|
await connection.end();
|
console.log('数据库连接已关闭');
|
}
|
}
|
}
|
|
// 运行修复脚本
|
fixWxLoginTable().catch(error => {
|
console.error('脚本执行失败:', error);
|
process.exit(1);
|
});
|
