ic-ctbu-backend/scripts/fix-collation.js

'use strict'

const mysql = require('mysql2/promise')
const config = require('../config.json').database

const TARGET_COLLATION = 'utf8mb4_general_ci'
const TARGET_CHARSET = 'utf8mb4'

async function main() {
    const conn = await mysql.createConnection({
        host: config.host,
        port: config.port,
        user: config.user,
        password: config.password,
        database: config.database,
        multipleStatements: true
    })

    try {
        const dbName = config.database

        const [schemaRows] = await conn.query(
            `SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
             FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?`,
            [dbName]
        )
        console.log('=== 库默认 ===', schemaRows[0])

        const [badTablesBefore] = await conn.query(
            `SELECT TABLE_NAME, TABLE_COLLATION
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'
               AND TABLE_COLLATION != ?
             ORDER BY TABLE_NAME`,
            [dbName, TARGET_COLLATION]
        )
        console.log(`=== 需转换的表: ${badTablesBefore.length} ===`)
        badTablesBefore.forEach((r) => console.log(`  ${r.TABLE_NAME} -> ${r.TABLE_COLLATION}`))

        console.log('\n=== 修改库默认字符集 ===')
        await conn.query(
            `ALTER DATABASE \`${dbName}\` CHARACTER SET ${TARGET_CHARSET} COLLATE ${TARGET_COLLATION}`
        )

        const [allTables] = await conn.query(
            `SELECT TABLE_NAME FROM information_schema.TABLES
             WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'
             ORDER BY TABLE_NAME`,
            [dbName]
        )

        console.log(`\n=== 开始转换 ${allTables.length} 张表 ===`)
        for (const row of allTables) {
            const table = row.TABLE_NAME
            process.stdout.write(`  CONVERT ${table} ... `)
            try {
                await conn.query(
                    `ALTER TABLE \`${dbName}\`.\`${table}\`
                     CONVERT TO CHARACTER SET ${TARGET_CHARSET} COLLATE ${TARGET_COLLATION}`
                )
                console.log('OK')
            } catch (err) {
                console.log('FAIL')
                console.error(`    ${err.message}`)
            }
        }

        const [badTablesAfter] = await conn.query(
            `SELECT TABLE_NAME, TABLE_COLLATION
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'
               AND TABLE_COLLATION != ?
             ORDER BY TABLE_NAME`,
            [dbName, TARGET_COLLATION]
        )
        const [badColsAfter] = await conn.query(
            `SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = ? AND COLLATION_NAME IS NOT NULL
               AND COLLATION_NAME != ?
             ORDER BY TABLE_NAME, ORDINAL_POSITION`,
            [dbName, TARGET_COLLATION]
        )

        console.log('\n=== 验证结果 ===')
        const [schemaAfter] = await conn.query(
            `SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
             FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?`,
            [dbName]
        )
        console.log('库默认:', schemaAfter[0])
        console.log(`剩余非 ${TARGET_COLLATION} 的表: ${badTablesAfter.length}`)
        badTablesAfter.forEach((r) => console.log(`  ${r.TABLE_NAME} -> ${r.TABLE_COLLATION}`))
        console.log(`剩余非 ${TARGET_COLLATION} 的列: ${badColsAfter.length}`)
        badColsAfter.slice(0, 20).forEach((r) =>
            console.log(`  ${r.TABLE_NAME}.${r.COLUMN_NAME} -> ${r.COLLATION_NAME}`)
        )

        console.log('\n=== 测试赠送审核 JOIN ===')
        try {
            await conn.query(
                `SELECT r.id
                 FROM lepao_send_count_request r
                 LEFT JOIN users su ON su.uuid = r.sender_uuid
                 LIMIT 1`
            )
            console.log('JOIN 测试: OK')
        } catch (err) {
            console.log('JOIN 测试: FAIL -', err.message)
        }
    } finally {
        await conn.end()
    }
}

main().catch((err) => {
    console.error(err.message || err)
    process.exit(1)
})