DELIMITER //
DROP PROCEDURE IF EXISTS proc_add;
CREATE PROCEDURE proc_add(
IN tab varchar(200),
IN colu varchar(200),
IN cus_type varchar(200))
BEGIN
set @sql = CONCAT('ALTER TABLE ',tab,' ADD COLUMN ',colu, ' ', cus_type); -- 预处理的sql语句,用concat拼接 注意这里的空格
set @sql2 = CONCAT('ALTER TABLE ',tab,' MODIFY COLUMN ',colu, ' ', cus_type);
IF NOT EXISTS (SELECT * FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = tab AND column_name = colu) THEN
PREPARE stmt FROM @sql;
EXECUTE stmt ;
deallocate prepare stmt;
ELSE
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2 ;
deallocate prepare stmt2;
END IF;
END //
DELIMITER ;
--DELIMITER 定好结束符 MYSQL的默认结束符为";"
--定义的函数proc_add 一旦创建成功 重启也不丢失
--mysql终端使用
CALL proc_add('t_server', 'config', 'VARCHAR(200)'); --表名 字段 类型
--pymysql 使用
class DBTool:
conn = None
cursor = None
def __init__(self):
self.conn = pymysql.connect(host=DB_HOST,
port=int(DB_PORT),
user=DB_USER,
passwd=DB_PASSWORD,
db=DB_NAME,
charset='utf8')
self.cursor = self.conn.cursor()
cursor = DBTool().cursor
cursor.callproc('proc_add', args=('t_server', 'config', 'VARCHAR(11)'))
print(cursor.fetchall())
mysql 添加字段前先做判断字段是否存在(处理添加字段重复报错)
最新推荐文章于 2024-03-19 20:32:42 发布