问题描述
我需要运行一个安装程序,该安装程序也可以是更新程序.无论某些表是否存在,缺少几列或由于其结构是最新的而无需更改,安装程序都必须最终具有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:
- 创建临时表,它是现有表的精确副本
- 用旧表中的数据填充临时表
- 丢掉旧桌子
- 使用新架构创建新表
- 使用临时表中的信息填充新表
- 删除临时表.
因此,在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
这篇关于更改表(如果存在)或创建(如果不存在)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!