Friday, May 6, 2011

MySQL create table script and Create procedure simple

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parentid` int(11) NOT NULL,
  `typecode` int(11) NOT NULL,
  `siteid` int(11) NOT NULL,
  `sectionid` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `template` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `photoid` int(11) NOT NULL,
  `ordering` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  `isactive` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


'delimiter ..' энэ команд нь үйлдэл гүйцэтгэх тусгаарлах тэмдэгт болох ';' -г '..' тэмдэгтээр солидог. mysql програмд нэвтрэн орж шууд бичин хэрэглэнэ. Араас нь доор байгаа кодыг ажиллуулах боломжтой болно.

drop procedure if exists addcolumnx6..
create procedure addcolumnx6(in tablename varchar(30))
begin
set @sql=CONCAT("alter table ",tablename," ");
set @sql=CONCAT(@sql," add column created datetime not null, ");
set @sql=CONCAT(@sql," add column createdid int(10) unsigned null, ");
set @sql=CONCAT(@sql," add column createdname varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, ");
set @sql=CONCAT(@sql," add column modified datetime not null, ");
set @sql=CONCAT(@sql," add column modifiedid int(10) unsigned null, ");
set @sql=CONCAT(@sql," add column modifiedname varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL; ");
PREPARE stm from @sql;
EXECUTE stm;
DEALLOCATE PREPARE stm;
end..

Бичсэн програмаа ажиллуулахдаа:
call AddColumnX6('category');

No comments: