Create a random alphanumeric string with mysql query

Why we need this?

We used to generate random strings for generating passwords or generating keys which are used for accessing sensitive information through a URL. When you want to generate a unique random key for a table, it is always better to generate the key in mysql rather than the server side scripting language. Generating random key in mysql procedure, avoid multiple key generation and checks the existence of generating a key, which reduces multiple mysql connection calls.

MySQL Custom Function to generate alphanumeric random string

Use the below code to create a custom function which generates a random key:

Note: The maximum length of random string that can be generated by the above function is 100. You can increase the limit by changing the function return type.

Using custom function in a procedure:

Implement the created custom function generate_random  :


Tips to change your MySQL Procedure through command shell

Drop your procedure before modifications

There is no alter command like table alter in mysql for modifying your MySQL procedure code. So you have to drop your procedure and then create the same procedure for altering your procedure. To drop the procedure, execute the following line of code in your command shell:

Note: Mysql ALTER PROCEDURE can be used to change the characteristics of a stored procedure only, not the parameters or body of the procedure.

Change the delimiter

Normally body of the procedure consists of semicolons (;) for the end of the line (statement). To execute the procedure as a single statement, you have to change the delimiter for the console (command line interface). You can change the delimiter to ‘/’ by executing the code below:

Execute the CREATE PROCEDURE code

Now you can type/copy your procedure code. An example procedure code is given below for your reference:

Note the last line of the procedure code. Your procedure will get executed only when you add the changed delimiter  / . If there are no issues with your code, your procedure will add successfully to the server.

Change to delimiter back to normal

You already added the procedure to the database, but make sure that you changed your delimiter back to the normal. Execute the following code for changing the delimiter to the default semicolon.


MySQL Delete Statement – Confusing Syntax Error when writes in one line

While writing mysql statements in your procedure, you may notice that the workbench will give you a syntax error warning when you states an error free delete statement as below:

The solution is simple, you have to split your statement in two lines. I was not able to find any document which specifies this scenario, but on every document, the delete statements are split into two lines. So the previous statement can be inserted to your procedure as:

Hope you enjoyed this tip. Please give you feedback as comments below.

Assign Variable Value from SELECT query for MySQL Procedure

You may be familiar with MySQL, and this article could be a known factor for you.  But many times, we miss the basics and ends up in approaching complex ways for simple things. This article is about a simple fact that we can miss while implementing a mysql procedure.

How to assign a variable?

In mysql we assign variable with the simple set  statement. Declaration of variable must be preceded before assigning the value (not necessary). A simple set statement is as follows:

 Assign SELECT query value to a variable

We can assign a variable in two ways. The first way is to use the set statement.

The same result can be achieved without the declaration.

You can do the same with SELECT... INTO  statement.