Mysql官方文档中争对安全添加列的处理方法。Mysql Add a Column to a table if not exists
来源:互联网 发布:淘宝云客服考试题库 编辑:程序博客网 时间:2024/06/02 06:31
Add a Column to a table if not exists
MySQL allows you to create a table if it does not exist, but does not provide a native way of a adding a column (i.e. a field) to an existing table with a test of whether the column already exists - so as to avoid an error if the column already exists. The ability to add a column if it does not exist can be useful for upgrade scripts and the like.
The following script creates a stored procedure that allows a column to be added to a table but only if it does not already exist:
drop procedure if exists AddColumnUnlessExists;create procedure AddColumnUnlessExists( IN dbName tinytext, IN tableName tinytext, IN fieldName tinytext, IN fieldDef text)begin IF NOT EXISTS ( SELECT * FROM information_schema.COLUMNS WHERE column_name=fieldName and table_name=tableName and table_schema=dbName ) THEN set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName, ' ADD COLUMN ',fieldName,' ',fieldDef); prepare stmt from @ddl; execute stmt; END IF;end;
This stored procedure provides the functionality for "add column if not exists". To use the script call it with the name of the database, name of the table, name of the field and the field definition to use if the field is to be created. For example:
call AddColumnUnlessExists(Database(), 'accounts', 'dob', 'varchar(32) null');which will add the field "
dob
" to the table "accounts
" in the current database, unless it already exists, orcall AddColumnUnlessExists('GIS', 'boundaries', 'fillColour', 'int unsigned not null default 1');
which will add the field "fillColour
" to the table "boundaries
" in the database "GIS
" if it does not already exist.
If you want to drop the stored procedure after use then use:
drop procedure AddColumnUnlessExists;
0 0
- Mysql官方文档中争对安全添加列的处理方法。Mysql Add a Column to a table if not exists
- MySQL对CREATE TABLE IF NOT EXISTS SELECT的处理
- MySQL How to add a column in a table?
- MySQL在指定列之前或之后插入列 (Add a column to an existing MySQL table)
- mysql的CREATE TABLE IF NOT EXISTS 方法
- mysql中if not exists的使用
- Add a column to table(Handling)
- mySQL Table 'A.bill' already exists
- MYSQL IF NOT EXISTS AND IF EXISTS
- MySQL: How to add column to existing table
- MySQL DROP TABLE IF EXISTS
- MySQL—— Insert record if not exists in table
- # mysql CREATE TABLE IF NOT EXISTS metadata lock坑
- mysql insert if not exists
- MySQL 表添加字段 存储过程实现 IF NOT EXISTS
- table if not exists
- How To Quickly Add/Remove Column Statistics (Histograms) For A Column (文档 ID 390249.1
- mysql insert if not exists防止插入重复记录的方法
- Java中元组的使用
- js-时间的转换问题
- MYsql分区分表
- Bundle类
- 2014版本USBasp固件升级,更换公版固件以支持ArduinoIDE
- Mysql官方文档中争对安全添加列的处理方法。Mysql Add a Column to a table if not exists
- CRF,HMM,MEMM
- 《深入浅出统计学》总结
- JavaScript如何一次性展示几万条数据
- java设计模式之---------------抽象工厂模式
- 寻找湖北荆州刘氏族谱
- Linux centOS目录结构详细版
- JavaWeb过滤器配置
- LINUX ulimit命令