Permalink: http://bit.ly/QuBLVB



Read part 1 for the rationale behind the code.

As discussed in the first part of this blog entry, we'll be utilizing a statement that uses base 36 to generate the random name. We will be adding the $ and _ characters using the ELT function. Here is a true random database and table name generator:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`(
 ) RETURNS varchar(64) CHARSET utf8
BEGIN
 DECLARE numberOfChars, charDiceRoll TINYINT(2);
 DECLARE charCount TINYINT DEFAULT 0;
 DECLARE randomChar CHAR(1);
 DECLARE randomName VARCHAR(64) DEFAULT '';
 …

Permalink: http://bit.ly/UZY7xT



Skip to part 2 to go straight to the code snippet.

See also a similar generator: Truly Random and Complex Password Generator

Database names and table names have certain restrictions in MySQL:

• The maximum name length for both are 64 characters
• Allowed characters are a-z, A-Z, 0-9, $, and _

It is possible to create a table or database with a dot (.) in its name, however this is not recommended as it will cause some of MySQL's built-in functions to not work as …

Permalink: http://bit.ly/R5rizQ



A function that can randomly pick an integer from a given range can be used by applications that need a number dice roll and random generation of strings, numbers, and even random generation of complex passwords. Let's say you would like to randomly pick a number from 1 to 10. This gives us 10 choices to randomly pick, not 9 as can be mistakenly thought of since 10 minus 1 equals 9. The same is with the range of choices from 0 to 10. This gives 11 possibilities, not 10. To illustrate:

• The range of choices should include the value of the lower end of the range
• It should …

Permalink: http://bit.ly/VP174V



Certain complex MySQL SELECT and subquery statements will not allow the use of the * wildcard and you will need to fill in the entire column list of a given table. Consider the following simplified example, a SELECT statement that contains 3 columns. The asterisk here refers to all columns, which is actually the 3 columns listed in the GROUP BY clause:

SELECT
 IF(
  EXISTS(
  SELECT *
  FROM (
   SELECT *
   FROM `dbName_A`.`tableName_A`
   UNION ALL
   SELECT *
   FROM `dbName_B`.`tableName_B`
   AS `compareTables`
  GROUP BY `column_1`, `column_2`, `column_3`
  HAVING COUNT(*) = 1),
 1, 0);

Imagine if it were dozens of columns instead of just 3. You can't simply put in the * wildcard like 'GROUP BY * '. The above example will not …

I have just seen Limiting table disk quota in MySQL by Shlomi Noach, and I could not resist.
You can actually implement a disk quota using an updatable view with the CHECK OPTION.
Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.

drop table if exists logs;
create table logs (t mediumtext) engine=innodb;

drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota() 
returns boolean
deterministic
return (
    select CASE 
           WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024) 
           THEN TRUE ELSE FALSE 
 …

What a great day! I see that the drizzle team has merged in my (and Padraig O'Sullivan's) memcached Functions for Drizzle (UDFs). I'm really glad to have this in drizzle now as it adds a means of interacting with memcached from within Drizzle. I have most functions from the MySQL branch implemented now as well. I'm extremely grateful to Padraig O'Sullivan for getting this project off the ground. I was a bit stuck with the new API and C++ when I first attempted these and he designed the class setup and had the major functions working which I then picked up and added more functions as well as tests. I was also glad to have the drizzle team add in my sleep() UDF which allowed me to test expirations in these memcached functions.

These functions have some similar, but very little code from the memcached Functions for MySQL. The new UDF API is completely different than MySQL's UDF API. You have to create a class for each function that is a …