本文介绍了更改表(如果存在)或创建(如果不存在)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要运行一个安装程序,该安装程序也可以是更新程序.无论某些表是否存在,缺少几列或由于其结构是最新的而无需更改,安装程序都必须最终具有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.

如何将ALTERCREATE完美地组合在一起?

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 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 Reference Manual: CREATE TABLE Syntax
  • MySQL Reference Manual: INSERT Syntax
  • MySQL Reference Manual: INSERT ... SELECT Syntax

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

08-11 08:14