×
全部 > AI技术 > AI编程 > AI代码生成 > 生成MySQL数据库迁移脚本

生成MySQL数据库迁移脚本

claude3.5 sonnet 20240620(Anthropic)

## Change Overview 为 `users` 表添加 `last_login` 字段,类型为 `datetime`,允许为空。 ## Up Migration Script ```sql -- 开始事务 START TRANSACTION; -- 检查表是否存在 IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'users') THEN -- 添加 last_login 字段 ALTER TABLE users ADD COLUMN last_login DATETIME NULL; END IF; -- 提交事务 COMMIT; ``` ## Down Migration Script ```sql -- 开始事务 START TRANSACTION; -- 检查表是否存在 IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'users') THEN -- 删除 last_login 字段 ALTER TABLE users DROP COLUMN last_login; END IF; -- 提交事务 COMMIT; ``` ## Transaction Handling 以上脚本使用了事务处理,确保在执行过程中发生错误时可以回滚。 ## Execution Protection 脚本中包含了 Guard Clauses,以确保在进行变更前检查表的存在性。

通义千问2.5-Coder-14B(阿里云百炼)

```markdown # Change Overview 本次变更目的是为`users`表添加一个名为`last_login`的新字段,该字段的数据类型为`datetime`,并且允许为空。 # Up Migration Script ```sql -- Check if the column already exists to avoid duplicate migration IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'last_login') THEN START TRANSACTION; BEGIN -- Add the new column to the users table ALTER TABLE users ADD COLUMN last_login DATETIME NULL; COMMIT; END; ELSE SELECT 'Column last_login already exists in table users.' AS message; END IF; ``` # Down Migration Script ```sql -- Check if the column exists before attempting to drop it IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'last_login') THEN START TRANSACTION; BEGIN -- Drop the column from the users table ALTER TABLE users DROP COLUMN last_login; COMMIT; END; ELSE SELECT 'Column last_login does not exist in table users.' AS message; END IF; ``` # Transaction Handling - The up and down migration scripts are wrapped in transactions to ensure atomicity. If any part of the script fails, the transaction will be rolled back. # Execution Protection - Guard clauses are used to check if the column already exists before attempting to add it, and if the column exists before attempting to drop it. This prevents errors and ensures idempotency. ```

gpt-4.1(OpenAI)

## Change Overview 本次变更将为 `users` 表添加一个名为 `last_login` 的字段,类型为 `DATETIME`,允许为空。该字段用于记录用户的最近登录时间。此变更有助于追踪用户活跃度等后续功能。 --- ## Up Migration Script ```sql -- Up Migration: Add 'last_login' column to 'users' table START TRANSACTION; -- Guard Clause: 检查表是否存在且列尚未添加 SET @table_exists := (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'users'); SET @column_exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'last_login'); -- 只有在表存在且列尚未添加时执行 IF @table_exists = 1 AND @column_exists = 0 THEN ALTER TABLE `users` ADD COLUMN `last_login` DATETIME NULL COMMENT '最近登录时间'; END IF; COMMIT; ``` --- ## Down Migration Script ```sql -- Down Migration: Remove 'last_login' column from 'users' table START TRANSACTION; -- Guard Clause: 检查表和last_login列是否存在 SET @table_exists := (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'users'); SET @column_exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'last_login'); -- 只有在表存在且列已经存在时执行 IF @table_exists = 1 AND @column_exists = 1 THEN ALTER TABLE `users` DROP COLUMN `last_login`; END IF; COMMIT; ``` --- ## Transaction Handling - 所有脚本均包裹在 `START TRANSACTION` 和 `COMMIT` 之间,确保变更原子性。如执行中途出错,可回滚事务,避免数据库进入不一致状态。 --- ## 执行保护(Guard Clause) - 通过 `information_schema` 检查 `users` 表是否存在及 `last_login` 字段是否已存在/不存在,避免重复执行或误操作。 --- > 该脚本可直接集成到CI/CD数据库迁移流程,确保执行安全和可逆性。

DeepSeek V3(Deepseek)

```markdown # Database Migration Script ## Change Overview **Purpose:** Add a new nullable `last_login` datetime column to the `users` table. ## Up Migration Script ```sql -- [UP] Add last_login column to users table START TRANSACTION; -- Guard clause to check if column doesn't exist SET @dbname = DATABASE(); SET @tablename = 'users'; SET @columnname = 'last_login'; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname) ) > 0, 'SELECT 1', -- Column exists, do nothing CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DATETIME NULL COMMENT "Timestamp of last user login";') )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists; COMMIT; ``` ## Down Migration Script ```sql -- [DOWN] Remove last_login column from users table START TRANSACTION; -- Guard clause to check if column exists SET @dbname = DATABASE(); SET @tablename = 'users'; SET @columnname = 'last_login'; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname) ) > 0, CONCAT('ALTER TABLE ', @tablename, ' DROP COLUMN ', @columnname, ';'), 'SELECT 1' -- Column doesn't exist, do nothing )); PREPARE alterIfExists FROM @preparedStatement; EXECUTE alterIfExists; DEALLOCATE PREPARE alterIfExists; COMMIT; ``` ## Transaction Handling - Both scripts are wrapped in explicit transactions (`START TRANSACTION`/`COMMIT`) - MySQL will automatically roll back if any error occurs during execution ## Execution Protection - Scripts include guard clauses checking column existence before operations - Uses prepared statements to prevent SQL injection - Safe for multiple executions (idempotent) ```

模型名称 模型提供商 响应总时长(s) 生成 token 数 首 token 延迟(s) 生成速率(tokens/s)
5.61
响应最快
0
2.09
0
8.09
329
0.36
延迟最小
40.68
8.21
576
内容最多
2.53
70.12
速度最快
28.4
522
4.88
18.38
AI文本生成
38家服务商提供服务
AI深度推理
11家服务商提供服务
AI代码生成
11家服务商提供服务
AI数学模型
10家服务商提供服务