MySQL Procedure To Get Incremental Numbers

9th March 2009 - 2 minutes read time

Run the following table definition to create a table called tests in the test database.

  1. DROP TABLE IF EXISTS test.tests;
  2. CREATE TABLE test.tests (
  3. id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. var VARCHAR(45) NOT NULL,
  5. PRIMARY KEY (id)
  6. );

Fill it with some default values:

INSERT INTO tests(var) VALUES('one'), ('two'), ('three'), ('four');

Now lets say that you want to find the values in the table, but that you want to increment a value for each row, the following procedure will do this. If you are using the MySQL Query browser then open a script table and past the following and execute, changing any database/table definitions.

  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `test`.`Increment` $$
  4. CREATE DEFINER=`root`@`localhost` PROCEDURE `Increment`( NUMBER CHAR(64) )
  5. BEGIN
  6.  
  7. SET @total=0;
  8. SELECT id, @total:=@total+NUMBER AS RunningSum FROM tests;
  9.  
  10. END $$
  11.  
  12. DELIMITER ;

You will now be able to call the procedure in the following way:

CALL INCREMENT(4);

The value that you pass to the procedure is used to work out the increment. So in this case the output would be something like the following:

  1. id RunningSum
  2. 1 4
  3. 2 8
  4. 3 12
  5. 4 16

 

Add new comment

The content of this field is kept private and will not be shown publicly.