MySQL 如果存在则更改表或如果不存在则创建
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16837134/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter table if exists or create if doesn't
提问by Alon_T
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.
我需要运行一个安装程序，它也可以是一个更新程序。安装程序需要能够最终拥有 mysql 数据库的特定方案/结构，无论某些表是否存在，是否遗漏了几列，或者不需要更改，因为它们的结构是最新的。
How can I make an elegant combination of ALTERand CREATE?
我怎样才能优雅地组合ALTER和CREATE？
I was thinking there must be something like "ADD... IF... Duplicate"
我在想一定有类似“添加......如果......重复”之类的东西
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.
假设我有表 A。在一个客户端中，该表有一个列 -A1，另一个客户端有相同的表，但有 A1 列和 A2 列。
I want my sql command to make both clients' table A hold three columns : A1, A2, and A3.
我希望我的 sql 命令使两个客户的表 A 都包含三列：A1、A2 和 A3。
Again, my script is a sql file that I dump to mysql.
同样，我的脚本是我转储到 mysql 的 sql 文件。
How do I do it? Thanks :-)
我该怎么做？谢谢 ：-）
回答by GregD
MySQL INFORMATION_SCHEMAdatabase to the rescue:
MySQLINFORMATION_SCHEMA数据库的救援：
-- 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;
More information:
更多信息：
- MySQL Reference Manual: Chapter 19. INFORMATION_SCHEMA Tables
- MySQL Reference Manual: The INFORMATION_SCHEMA TABLES Table
- MySQL Reference Manual: The INFORMATION_SCHEMA COLUMNS Table
- MySQL 参考手册：第 19 章 INFORMATION_SCHEMA 表
- MySQL 参考手册：INFORMATION_SCHEMA TABLES 表
- MySQL 参考手册：INFORMATION_SCHEMA COLUMNS 表
UPDATE:
更新：
Another option, possibly easier, is to drop the existing table and re-create it again with the new schema. To do this, you need:
另一种可能更简单的选择是删除现有表并使用新模式重新创建它。为此，您需要：
- Create temporary table, an exact copy of the existing table
- Populate the temporary table with the data from the old table
- Drop the old table
- Create the new table with new schema
- Populate the new table with the information from the temporary table
- Drop temporary table.
- 创建临时表，现有表的精确副本
- 用旧表中的数据填充临时表
- 放下旧桌子
- 使用新架构创建新表
- 使用临时表中的信息填充新表
- 删除临时表。
So, in SQL code:
所以，在 SQL 代码中：
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".
其实最后一步，“删除临时表。”，你可以跳过一段时间。以防万一，您需要对旧表进行某种备份，“以防万一”。
More information:
更多信息：
- MySQL Reference Manual: CREATE TABLE Syntax
- MySQL Reference Manual: INSERT Syntax
- MySQL Reference Manual: INSERT ... SELECT Syntax
- MySQL 参考手册：CREATE TABLE 语法
- MySQL 参考手册：插入语法
- MySQL 参考手册：INSERT ... SELECT 语法
回答by We0
Well if you are using a coding language do:
好吧，如果您使用的是编码语言，请执行以下操作：
SHOW TABLES LIKE 'myTable'
if it return a value call the alter else call the create
如果它返回一个值调用alter else调用create
