如果存在则更改表或如果不存在则创建 [英] Alter table if exists or create if doesn't

问题描述

我需要运行一个安装程序，它也可以是一个更新程序.安装程序需要能够最终拥有 mysql 数据库的特定方案/结构，无论某些表是否存在，是否遗漏了几列，或者不需要更改，因为它们的结构是最新的.

I need to run an installer which can also be an updater. The installer needs to be able to end up having a certain scheme/structure of the mysql database, regardless if some of the tables existed, missed a few columns, or need not to be changed because their structure is up to date.

如何将 ALTER 和 CREATE 巧妙结合起来?

How can I make an elegant combination of ALTER and CREATE?

我在想一定有类似添加...如果...重复"之类的东西

I was thinking there must be something like "ADD... IF... Duplicate"

假设我有表 A.在一个客户端中，该表有一个列 -A1，另一个客户端有相同的表，但有 A1 列和 A2 列.

Say I have table A. In one client the table has one column -A1, and another client has the same table but with column A1 and column A2.

我希望我的 sql 命令使两个客户的表 A 都包含三列:A1、A2 和 A3.

I want my sql command to make both clients' table A hold three columns : A1, A2, and A3.

同样，我的脚本是我转储到 mysql 的 sql 文件.

Again, my script is a sql file that I dump to mysql.

我该怎么做?谢谢:-)

How do I do it? Thanks :-)

推荐答案

MySQL INFORMATION_SCHEMA 数据库的救援:

MySQL INFORMATION_SCHEMA database to the rescue:

-- First check if the table exists
IF EXISTS(SELECT table_name 
            FROM INFORMATION_SCHEMA.TABLES
           WHERE table_schema = 'db_name'
             AND table_name LIKE 'wild')

-- If exists, retreive columns information from that table
THEN
   SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
     FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'tbl_name'
      AND table_schema = 'db_name';

   -- do some action, i.e. ALTER TABLE if some columns are missing 
   ALTER TABLE ...

-- Table does not exist, create a new table
ELSE
   CREATE TABLE ....

END IF;

更多信息:

• MySQL 参考手册:第 19 章 INFORMATION_SCHEMA 表
• MySQL 参考手册:INFORMATION_SCHEMA TABLES 表
• MySQL 参考手册:INFORMATION_SCHEMA COLUMNS 表

更新:

另一个可能更简单的选择是删除现有表并使用新模式重新创建它.为此，您需要:

Another option, possibly easier, is to drop the existing table and re-create it again with the new schema. To do this, you need:

1. 创建临时表，现有表的精确副本
2. 用旧表中的数据填充临时表
3. 放下旧桌子
4. 使用新架构创建新表
5. 用临时表中的信息填充新表
6. 删除临时表.

所以，在 SQL 代码中:

So, in SQL code:

CREATE TABLE old_table_copy LIKE old_table;

INSERT INTO old_table_copy
SELECT * FROM old_table;

DROP TABLE old_table;

CREATE TABLE new_table (...new values...);

INSERT INTO new_table ([... column names from old table ...])
SELECT [...column names from old table ...] 
FROM old_table_copy;

DROP TABLE old_table_copy;

其实最后一步，删除临时表."，你可以跳过一段时间.以防万一，您希望对旧表进行某种备份，以防万一".

Actually the last step, "Drop temporary table.", you could skip for a while. Just in case, you would want to have some sort of backup of the old table, "just-in-case".

更多信息:

• MySQL 参考手册:CREATE TABLE 语法
• MySQL 参考手册:插入语法
• MySQL 参考手册:INSERT ... SELECT 语法

这篇关于如果存在则更改表或如果不存在则创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！