栏目导航
热点推荐
- mysql排序的妙用(类似置顶)
- 优化MySQL的数据库性能的八种方
- MySQL配置文件my.cnf中文版
- linux下mysql命令
- 如何用MySQL-Front远程连接MySql
- 如何修改MySQL 5.1 data文件夹路
- Linux mysql安装修改root密码服
- MySQL Server 5.0安装教程(图解)
- PHP中的MYSQL常用函数
- mysql常见错误提示及解决方法
- MySQL常见错误问答!
- 使用MySQL时的一些常见错误
阅览排行
mysql 获取表有多少列
www.jz123.cn 2010-10-12 来源: 中国建站 责任编辑(袁袁) 我要投递新闻
【代码示例】
[code=SQL]
DELIMITER $$
DROP FUNCTION IF EXISTS `db_waasai`.`CheckIsObjectExist`$$
CREATE FUNCTION `db_waasai`.`CheckIsObjectExist`(table_name VARCHAR(50),colume_name varchar(50))
RETURNS BOOLEAN
BEGIN
DECLARE t_count TINYINT UNSIGNED;
SELECT count(0) INTO t_count FROM information_schema.COLUMNS where TABLE_NAME=table_name && (isnull(colume_name) || colume_name= ' ' || COLUMN_NAME=colume_name);
RETURN t_count> 0;
END$$
DROP PROCEDURE if EXISTS `db_waasai`.`sp_update_106_107` $$
CREATE PROCEDURE `db_waasai`.`sp_update_106_107`()
BEGIN
IF NOT CheckIsObjectExist( 'tbl_user_card ', ' ') THEN
CREATE TABLE `tbl_user_card` (
`u_id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`u_status` TINYINT(1) UNSIGNED DEFAULT '0 ',
`u_name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`u_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;
END IF;
IF NOT CheckIsObjectExist( 'tbl_user ', 'u_cards_num ') THEN
alter table `tbl_user` add column `u_cards_num` int (12) UNSIGNED DEFAULT '0 ' NULL after `u_age`;
update `tbl_user` set `u_cards_num`=1;
END IF;
END$$
DELIMITER ;
CALL sp_update_106_107();
DROP PROCEDURE IF EXISTS `lsgw`.`sp_update_106_107`;
[code=SQL]
DELIMITER $$
DROP FUNCTION IF EXISTS `db_waasai`.`CheckIsObjectExist`$$
CREATE FUNCTION `db_waasai`.`CheckIsObjectExist`(table_name VARCHAR(50),colume_name varchar(50))
RETURNS BOOLEAN
BEGIN
DECLARE t_count TINYINT UNSIGNED;
SELECT count(0) INTO t_count FROM information_schema.COLUMNS where TABLE_NAME=table_name && (isnull(colume_name) || colume_name= ' ' || COLUMN_NAME=colume_name);
RETURN t_count> 0;
END$$
DROP PROCEDURE if EXISTS `db_waasai`.`sp_update_106_107` $$
CREATE PROCEDURE `db_waasai`.`sp_update_106_107`()
BEGIN
IF NOT CheckIsObjectExist( 'tbl_user_card ', ' ') THEN
CREATE TABLE `tbl_user_card` (
`u_id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`u_status` TINYINT(1) UNSIGNED DEFAULT '0 ',
`u_name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`u_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;
END IF;
IF NOT CheckIsObjectExist( 'tbl_user ', 'u_cards_num ') THEN
alter table `tbl_user` add column `u_cards_num` int (12) UNSIGNED DEFAULT '0 ' NULL after `u_age`;
update `tbl_user` set `u_cards_num`=1;
END IF;
END$$
DELIMITER ;
CALL sp_update_106_107();
DROP PROCEDURE IF EXISTS `lsgw`.`sp_update_106_107`;
0
上一篇:MySQL 定时器EVENT学习 下一篇:MySQL分区的功能和限制讲解