位置:海鸟网 > IT > mySQL >

mysql 获取表有多少列

   【代码示例】
[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`;