更改表(如果存在)或创建(如果不存在) [英] 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?

我当时想应该有类似"ADD ... IF ... Duplicate"之类的东西

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列表

• MySQL Reference Manual: Chapter 19. INFORMATION_SCHEMA Tables
• MySQL Reference Manual: The INFORMATION_SCHEMA TABLES Table
• MySQL Reference Manual: The INFORMATION_SCHEMA COLUMNS Table

更新:

另一种可能更容易的选择是删除现有表，然后使用新的架构重新创建它.为此，您需要:

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参考手册:创建表语法
• MySQL参考手册: INSERT语法
• MySQL参考手册: INSERT ... SELECT语法

• MySQL Reference Manual: CREATE TABLE Syntax
• MySQL Reference Manual: INSERT Syntax
• MySQL Reference Manual: INSERT ... SELECT Syntax

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