PlanetMysql.ru — информация о СУБД MySQL

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

1. Create the table to store the current sequence values:

   CREATE TABLE _sequence
   (
       seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
       seq_group VARCHAR(10) NOT NULL,
       seq_val INT UNSIGNED NOT NULL
   );

2. Create a function to get and increment the current value:

   delimiter //
   DROP FUNCTION IF EXISTS getNextCustomSeq//
   CREATE FUNCTION getNextCustomSeq
   (
       sSeqName VARCHAR(50),
       sSeqGroup VARCHAR(10)
   ) RETURNS VARCHAR(20)
   BEGIN
       DECLARE nLast_val INT; 
    
       SET nLast_val =  (SELECT seq_val
                             FROM _sequence
                             WHERE seq_name = sSeqName
                                   AND seq_group = sSeqGroup);
       IF nLast_val IS NULL THEN
           SET nLast_val = 1;
           INSERT INTO _sequence (seq_name,seq_group,seq_val)
           VALUES (sSeqName,sSeqGroup,nLast_Val);
       ELSE
           SET nLast_val = nLast_val + 1;
           UPDATE _sequence SET seq_val = nLast_val
           WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
       END IF; 
    
       SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
       RETURN @ret;
   END// 
    
   delimiter ;

3. Create a stored procedure to modify a current sequence value:

   delimiter //
   DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
   CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
                 sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
   BEGIN
       IF (SELECT COUNT(*) FROM _sequence  
               WHERE seq_name = sSeqName  
                   AND seq_group = sSeqGroup) = 0 THEN
           INSERT INTO _sequence (seq_name,seq_group,seq_val)
           VALUES (sSeqName,sSeqGroup,nVal);
       ELSE
           UPDATE _sequence SET seq_val = nVal
           WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
       END IF;
   END//
   delimiter ;

Testing the new functions:

1. Create a table:

   CREATE TABLE custom_autonums
   (
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      seq_1 VARCHAR(20), -- custom sequence 1
      seq_2 VARCHAR(20), -- custom sequence 2
      UNIQUE(seq_1),
      UNIQUE(seq_2)
   );

2. Create trigger:

   delimiter //
   DROP TRIGGER IF EXISTS custom_autonums_bi//
    
   CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
   FOR each ROW
   BEGIN
      SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
      SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
   END//
    
   delimiter ;

3. Insert some values:

   INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
   SELECT * FROM custom_autonums;
   +----+------------+------------+
   | id | seq_1      | seq_2      |
   +----+------------+------------+
   |  4 | 001-000001 | DBA-000001 |
   |  5 | 001-000002 | DBA-000002 |
   |  6 | 001-000003 | DBA-000003 |
   +----+------------+------------+
   3 ROWS IN SET (0.00 sec)

4. Altering current values:

   CALL sp_setCustomVal('seq_1','001',675);
    
   INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
   SELECT * FROM custom_autonums;
   +----+------------+------------+
   | id | seq_1      | seq_2      |
   +----+------------+------------+
   |  4 | 001-000001 | DBA-000001 |
   |  5 | 001-000002 | DBA-000002 |
   |  6 | 001-000003 | DBA-000003 |
   |  7 | 001-000676 | DBA-000004 |
   |  8 | 001-000677 | DBA-000005 |
   |  9 | 001-000678 | DBA-000006 |
   +----+------------+------------+
   6 ROWS IN SET (0.00 sec)

Enjoy!

PlanetMySQL Voting: Vote UP / Vote DOWN

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

1. Create the table to store the current sequence values:

   CREATE TABLE _sequence
   (
       seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
       seq_group VARCHAR(10) NOT NULL,
       seq_val INT UNSIGNED NOT NULL
   );

2. Create a function to get and increment the current value:

   delimiter //
   DROP FUNCTION IF EXISTS getNextCustomSeq//
   CREATE FUNCTION getNextCustomSeq
   (
       sSeqName VARCHAR(50),
       sSeqGroup VARCHAR(10)
   ) RETURNS VARCHAR(20)
   BEGIN
       DECLARE nLast_val INT; 
    
       SET nLast_val =  (SELECT seq_val
                             FROM _sequence
                             WHERE seq_name = sSeqName
                                   AND seq_group = sSeqGroup);
       IF nLast_val IS NULL THEN
           SET nLast_val = 1;
           INSERT INTO _sequence (seq_name,seq_group,seq_val)
           VALUES (sSeqName,sSeqGroup,nLast_Val);
       ELSE
           SET nLast_val = nLast_val + 1;
           UPDATE _sequence SET seq_val = nLast_val
           WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
       END IF; 
    
       SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
       RETURN @ret;
   END// 
    
   delimiter ;

3. Create a stored procedure to modify a current sequence value:

   delimiter //
   DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
   CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
                 sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
   BEGIN
       IF (SELECT COUNT(*) FROM _sequence  
               WHERE seq_name = sSeqName  
                   AND seq_group = sSeqGroup) = 0 THEN
           INSERT INTO _sequence (seq_name,seq_group,seq_val)
           VALUES (sSeqName,sSeqGroup,nVal);
       ELSE
           UPDATE _sequence SET seq_val = nVal
           WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
       END IF;
   END//
   delimiter ;

Testing the new functions:

1. Create a table:

   CREATE TABLE custom_autonums
   (
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      seq_1 VARCHAR(20), -- custom sequence 1
      seq_2 VARCHAR(20), -- custom sequence 2
      UNIQUE(seq_1),
      UNIQUE(seq_2)
   );

2. Create trigger:

   delimiter //
   DROP TRIGGER IF EXISTS custom_autonums_bi//
    
   CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
   FOR each ROW
   BEGIN
      SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
      SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
   END//
    
   delimiter ;

3. Insert some values:

   INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
   SELECT * FROM custom_autonums;
   +----+------------+------------+
   | id | seq_1      | seq_2      |
   +----+------------+------------+
   |  4 | 001-000001 | DBA-000001 |
   |  5 | 001-000002 | DBA-000002 |
   |  6 | 001-000003 | DBA-000003 |
   +----+------------+------------+
   3 ROWS IN SET (0.00 sec)

4. Altering current values:

   CALL sp_setCustomVal('seq_1','001',675);
    
   INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
   SELECT * FROM custom_autonums;
   +----+------------+------------+
   | id | seq_1      | seq_2      |
   +----+------------+------------+
   |  4 | 001-000001 | DBA-000001 |
   |  5 | 001-000002 | DBA-000002 |
   |  6 | 001-000003 | DBA-000003 |
   |  7 | 001-000676 | DBA-000004 |
   |  8 | 001-000677 | DBA-000005 |
   |  9 | 001-000678 | DBA-000006 |
   +----+------------+------------+
   6 ROWS IN SET (0.00 sec)

Enjoy!

PlanetMySQL Voting: Vote UP / Vote DOWN

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 derived item class. Gone are the function_init, funciton (value), function_deinit setup-- now the main logic is all implemented in value *method*.

These functions are really useful: You aren't tied to a particular client language. Say for instance you have an application that doesn't yet have a client to Drizzle (though, most languages are supported). This solves that problem in that the UDFs use libmemcached. You can also construct queries that use a table of IDs (fast lookup) to pull from memcached several items at once in an aggregate query which is really useful. You could effectively use drizzle as a relational organizer of data you have put into a memcached cloud.

I'll be continuing to add features from the MySQL memcached functions as well as add the work I've completed need to get released, which is utilizing Trond Norbye's great work with connection pooling, which Padraig and I will eventually want to use one of the C++ map classes to solve.

You can use these functions, as well as Drizzle by visiting:

https://code.launchpad.net/drizzle
PlanetMySQL Voting: Vote UP / Vote DOWN