Crappy loop inside mysql request… Play with mysql procedure.
If you miss something during the conception of your mysql database, or wish to add some new tables for additional features in your web application, it could be a mess to build the new version on your database without droping all data of the existing tables.
I personnaly needed to keep thousand of registered users (in a table) and nevertheless i had to provide some messaging feature in another table.
I decided to:
1. Create the new table in my existing database:
CREATE TABLE `newone` (
`id` INT NOT NULL ,
`field1` TINYINT NOT NULL ,
`field2` BLOB NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;
2. Set my default values for each field:
ALTER TABLE `newone` CHANGE `field1` `field1` TINYINT( 4 ) NOT NULL DEFAULT ‘999999’
3. Insert as many lines as registered user, so that every registered user have their own line in the « newone » table:
i used the LOOP mysql statement to achieve this:
CREATE PROCEDURE massiveInsert(IN maxInserts INT)
DECLARE i INT;
IF i=maxInserts THEN
insert into newone values();
END LOOP myloop;
Doesn’t it looks like 80’s GW*-Basic, ehhh ?
To run this procedure, just type:
What do you need to remind concerning MySQL procedures:
– Define you OWN delimiter, unsing the delimiter. Otherwise, you will get the Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near blahblahblah
– Use the keyword IN to input parameters, and next the variable name + parameter type.
– Use the call() instruction to execute your procedure.