97 lines
3.0 KiB
JavaScript
97 lines
3.0 KiB
JavaScript
// 檢查資料庫結構
|
|
require('dotenv').config({ path: '.env.local' })
|
|
const mysql = require('mysql2/promise')
|
|
|
|
async function checkDatabaseStructure() {
|
|
const config = {
|
|
host: process.env.DB_HOST,
|
|
port: parseInt(process.env.DB_PORT),
|
|
database: process.env.DB_DATABASE,
|
|
user: process.env.DB_USERNAME,
|
|
password: process.env.DB_PASSWORD,
|
|
}
|
|
|
|
try {
|
|
const connection = await mysql.createConnection(config)
|
|
console.log('🔗 連接到 MySQL 資料庫...')
|
|
|
|
// 檢查所有表
|
|
console.log('\n📋 檢查資料庫表結構...')
|
|
const [tables] = await connection.execute(`
|
|
SELECT table_name
|
|
FROM information_schema.tables
|
|
WHERE table_schema = ?
|
|
`, [config.database])
|
|
|
|
console.log(`\n📊 找到 ${tables.length} 個表:`)
|
|
|
|
for (const table of tables) {
|
|
const tableName = table.table_name
|
|
console.log(`\n🔍 表: ${tableName}`)
|
|
|
|
if (!tableName) {
|
|
console.log(' ⚠️ 表名為空，跳過')
|
|
continue
|
|
}
|
|
|
|
// 檢查表結構
|
|
const [columns] = await connection.execute(`
|
|
SELECT column_name, data_type, is_nullable, column_default, column_key
|
|
FROM information_schema.columns
|
|
WHERE table_schema = ? AND table_name = ?
|
|
ORDER BY ordinal_position
|
|
`, [config.database, tableName])
|
|
|
|
console.log(' 欄位:')
|
|
columns.forEach(col => {
|
|
const key = col.column_key ? ` (${col.column_key})` : ''
|
|
const nullable = col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'
|
|
console.log(` - ${col.column_name}: ${col.data_type} ${nullable}${key}`)
|
|
})
|
|
|
|
// 檢查外鍵
|
|
const [foreignKeys] = await connection.execute(`
|
|
SELECT
|
|
constraint_name,
|
|
column_name,
|
|
referenced_table_name,
|
|
referenced_column_name
|
|
FROM information_schema.key_column_usage
|
|
WHERE table_schema = ?
|
|
AND table_name = ?
|
|
AND referenced_table_name IS NOT NULL
|
|
`, [config.database, tableName])
|
|
|
|
if (foreignKeys.length > 0) {
|
|
console.log(' 外鍵:')
|
|
foreignKeys.forEach(fk => {
|
|
console.log(` - ${fk.constraint_name}: ${fk.column_name} -> ${fk.referenced_table_name}.${fk.referenced_column_name}`)
|
|
})
|
|
}
|
|
|
|
// 檢查索引
|
|
const [indexes] = await connection.execute(`
|
|
SELECT index_name, column_name, non_unique
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = ? AND table_name = ?
|
|
ORDER BY index_name, seq_in_index
|
|
`, [config.database, tableName])
|
|
|
|
if (indexes.length > 0) {
|
|
console.log(' 索引:')
|
|
indexes.forEach(idx => {
|
|
const unique = idx.non_unique === 0 ? 'UNIQUE' : ''
|
|
console.log(` - ${idx.index_name}: ${idx.column_name} ${unique}`)
|
|
})
|
|
}
|
|
}
|
|
|
|
await connection.end()
|
|
console.log('\n✅ 資料庫結構檢查完成')
|
|
|
|
} catch (error) {
|
|
console.error('❌ 檢查資料庫結構失敗:', error)
|
|
}
|
|
}
|
|
|
|
checkDatabaseStructure()
|