轶哥

妄图改变世界的全栈程序员。

MySQL批量重命名表名和列名
  •   更新:2022-02-23 20:16:12
  •   首发:2022-02-23 20:16:12
  •   源代码
  •   573

MySQL批量重命名表名和列名,修改表名为大驼峰,列名为小驼峰。

最近公司项目重构,为了适应Prisma的习惯,保持数据库字段命名方式和代码模式一致,特地编写此脚本实现批量重命名已存在的表名和列名。

import mysql from 'mysql2/promise'
import toPascalCase from 'js-pascalcase'
import toCamelCase from 'js-camelcase'

const onlyShowSQL = true

// create the connection to database
const connection = await mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'root',
  database: 'example',
  password: 'password'
})

const [results] = await connection.query('SHOW TABLES;')

const tableNameList = results.map(i => Object.values(i)[0])

tableNameList.forEach(async tableName => {
  const sql = "ALTER TABLE `" + tableName + "` RENAME TO `" + toPascalCase(tableName) + "`;"
  if (onlyShowSQL) {
    console.log(sql)
  } else {
    await connection.query(sql)
  }

  const [columnList] = await connection.query("SHOW COLUMNS FROM `" + (onlyShowSQL ? tableName : toPascalCase(tableName)) + "`;")

  columnList.forEach(async column => {
    const sql = "ALTER TABLE `" + tableName + "` RENAME COLUMN`" + column.Field + "` TO `" + toCamelCase(column.Field) + "`;"
    if (onlyShowSQL)
      console.log(sql)
    else {
      await connection.query(sql)
    }
  })
})

console.log('Finished.')

Require Node.js >= 17.6.0

打赏
交流区

暂无内容

尚未登陆
发布
  上一篇 (MySQL导入数据库中文乱码问题解决)
下一篇 (联发科芯片手机刷入Magisk boot教程(解决无限重启))  

评论回复提醒