MySQL Procedure To Get Incremental Numbers

9th March 2009 - 2 minutes read time

Note: This post is over a year old and so the information contained here might be out of date. If you do spot something please leave a comment and I will endeavour to correct.

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

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

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.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`Increment` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Increment`( NUMBER CHAR(64) )
BEGIN
 
SET @total=0;
SELECT id, @total:=@total+NUMBER AS RunningSum FROM tests;
 
END $$
 
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:

id  RunningSum
1   4
2   8
3   12
4   16

numbers

MySQL

procedure

Add new comment

Case Insensitive Like Searches In MySQL

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

Connecting To A Non Standard MySQL Socket In PHP

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details. A good example of this is when connecting to a local MySQL server through a different (or at least non standard) socket. The normal place for the MySQL socket to be on a Linux install is /var/run/mysqld/mysqld.sock, but some hosts might change this.

Count Number Of Characters In A String With MySQL

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL. I found an alternative method, but it got me thinking on how that might be possible.

Finding the solution wasn't too difficult really, all I needed to do was find out the length of the URL string and then subtract the length of the string with the slashes removed. This MySQL statement will give you the URL and the number of slashes in that URL in a table.

MySQL Order Table By Character Length

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.

SELECT FIELD, CHARACTER_LENGTH(FIELD) AS fieldCharacterCount
FROM TABLE
ORDER BY fieldCharacterCount DESC

Backup MySQL Database PHP Script

There are quite a few scripts available on the Internet that allow you to dump data from a database into a format that can be used to replicate that database structure elsewhere. The following function is my take on this commonly occurring script.