Compiling the database upgrade script under MySQL

1 stored procedures are required for upgrade

1 /**/2 Drop procedure if existsPro_upgrade;3DELIMITER//4 CREATEDefiner=' Root ' @ '%`PROCEDURE' Pro_upgrade ' (5Exec_boolenint   ,6Sql_strVARCHAR( the)  7 )8 BEGIN    9 # Execute SQL OperationTen     IFSql_str<>"'  One      Then   A       Set @sql1 =Sql_str; -       Set @bexec =Exec_boolen;  -          the         if @bexec = 0 -          Then  -             PREPAREExecsql from @sql1;  -             EXECUTEExecsql; +         End if; -     END IF; + END// ADELIMITER;

View Code

This stored procedure is primarily a validation of the existence of a table field

2 Determine if a library exists

1 CREATE DATABASE IF  not EXISTS /* */; 2  use ' fqmanagesysdb ';

View Code

3 Determining whether a table exists for creation

1 CREATE TABLE IF  not EXISTS' UserInfo ' (2' ID 'int( One) not NULL DEFAULT '1',3`user_name`varchar(255) not NULL,4' User_pwd 'varchar(255) not NULL,5' User_type 'tinyint(4) not NULL DEFAULT '2',6`Power`int( One) not NULL DEFAULT '0',7' DeptID 'int( One) not NULL DEFAULT '0'8   PRIMARY KEY(`user_name' , ' user_type '),9   KEY' Userinfo_user_name_index ' ('user_name`)Ten) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

View Code

4 Judging whether the view exists

1 DROP VIEW IF EXISTS ' Allcode_view ';

5 determining if a stored procedure exists

1 drop procedure if exists procedure_split;

6 Inserting initialization data

1 INSERT  into' UserInfo ' (ID,user_name, User_pwd,user_type,Power, DeptID)Select '1','Admin','Admin','1','0','1'  fromDUALwhere  not exists(Select *  fromUserInfowhere user_name = 'Admin');

7 determining if a trigger exists

1 DROP TRIGGER IF EXISTS ' Trigger_delete_fucode ';

8 Adding a new field to the table

1 SELECT Count(*) fromInformation_schema. COLUMNSWHERETable_schema='Fqmanagesysdb'  andtable_name='UserInfo'  andcolumn_name='Brokerid'  into @ret;2Call Pro_upgrade (@ret,'ALTER TABLE userinfo ADD COLUMN brokerid varchar (255) Not NULL');

This uses the above stored procedure, MySQL inside the non-stored procedure inside does not support if not EXISTS's judgment

9 Other

I want to add ...

Compiling the database upgrade script under MySQL