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!

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!

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       |
+----------------------------+--------+-------+---------+-----------------+
| actor                      | InnoDB | 200   | 0.03 Mb | utf8_general_ci |
| actor_info                 | [VIEW] | -     | -       | -               |
| address                    | InnoDB | 589   | 0.09 Mb | utf8_general_ci |
| category                   | InnoDB | 16    | 0.02 Mb | utf8_general_ci |
| city                       | InnoDB | 427   | 0.06 Mb | utf8_general_ci |
| country                    | InnoDB | 109   | 0.02 Mb | utf8_general_ci |
| customer                   | InnoDB | 541   | 0.12 Mb | utf8_general_ci |
| customer_list              | [VIEW] | -     | -       | -               |
| film                       | InnoDB | 1131  | 0.27 Mb | utf8_general_ci |
| film_actor                 | InnoDB | 5143  | 0.27 Mb | utf8_general_ci |
| film_category              | InnoDB | 316   | 0.08 Mb | utf8_general_ci |
| film_list                  | [VIEW] | -     | -       | -               |
| film_text                  | MyISAM | 1000  | 0.31 Mb | utf8_general_ci |
| inventory                  | InnoDB | 4673  | 0.36 Mb | utf8_general_ci |
| language                   | InnoDB | 6     | 0.02 Mb | utf8_general_ci |
| nicer_but_slower_film_list | [VIEW] | -     | -       | -               |
| payment                    | InnoDB | 15422 | 2.12 Mb | utf8_general_ci |
| rental                     | InnoDB | 15609 | 2.72 Mb | utf8_general_ci |
| sales_by_film_category     | [VIEW] | -     | -       | -               |
| sales_by_store             | [VIEW] | -     | -       | -               |
| staff                      | InnoDB | 1     | 0.09 Mb | utf8_general_ci |
| staff_list                 | [VIEW] | -     | -       | -               |
| store                      | InnoDB | 2     | 0.05 Mb | utf8_general_ci |
+----------------------------+--------+-------+---------+-----------------+
23 rows in set (0.04 sec)
 
+----------------------------+-----------+---------------------+
| Routine Name               | Type      | Comment             |
+----------------------------+-----------+---------------------+
| get_customer_balance       | FUNCTION  |                     |
| inventory_held_by_customer | FUNCTION  |                     |
| inventory_in_stock         | FUNCTION  |                     |
| film_in_stock              | PROCEDURE |                     |
| film_not_in_stock          | PROCEDURE |                     |
| rewards_report             | PROCEDURE |                     |
| customer_create_date       | TRIGGER   | On INSERT: customer |
| del_film                   | TRIGGER   | On DELETE: film     |
| ins_film                   | TRIGGER   | On INSERT: film     |
| payment_date               | TRIGGER   | On INSERT: payment  |
| rental_date                | TRIGGER   | On INSERT: rental   |
| upd_film                   | TRIGGER   | On UPDATE: film     |
+----------------------------+-----------+---------------------+
12 rows in set (0.04 sec)
 
Query OK, 0 rows affected (0.04 sec)

There is the procedure source code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
 
CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
 
BEGIN 
 
 
 
-- Obtaining tables and views
 
(
 
    SELECT 
 
     TABLE_NAME AS `Table Name`, 
 
     ENGINE AS `Engine`,
 
     TABLE_ROWS AS `Rows`,
 
     CONCAT(
 
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
 
        , ' Mb')
 
       AS `Size`,
 
     TABLE_COLLATION AS `Collation`
 
    FROM information_schema.TABLES
 
    WHERE TABLES.TABLE_SCHEMA = dbname
 
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
 
)
 
UNION
 
(
 
    SELECT 
 
     TABLE_NAME AS `Table Name`, 
 
     '[VIEW]' AS `Engine`,
 
     '-' AS `Rows`,
 
     '-' `Size`,
 
     '-' AS `Collation`
 
    FROM information_schema.TABLES
 
    WHERE TABLES.TABLE_SCHEMA = dbname 
 
          AND TABLES.TABLE_TYPE = 'VIEW'
 
)
 
ORDER BY 1;
 
 
 
-- Obtaining functions, procedures and triggers
 
(
 
    SELECT ROUTINE_NAME AS `Routine Name`, 
 
     ROUTINE_TYPE AS `Type`,
 
     '' AS `Comment`
 
    FROM information_schema.ROUTINES
 
    WHERE ROUTINE_SCHEMA = dbname
 
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
 
)
 
UNION
 
(
 
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
 
    FROM information_schema.TRIGGERS
 
    WHERE EVENT_OBJECT_SCHEMA = dbname
 
)
 
ORDER BY 2,1;
 
 
 
END$$
 
delimiter ;

To use in your place you must call as:

mysql> call tools.sp_status(database());

Note the stored procedure has created in tools database (you can use another db), the goal of this is to call that useful procedure from any database, and it receives the name of database as parameter because is not possible obtain the current database from inside of stored procedure.

I published that code on  MySQL custom show table status entry at forge.mysql.com.

Any ideas to improving the source code to make it more useful?

`

1. Ideas for select all columns but one mysql stored procedure Assume we’ve a table with 100 rows and we need...
2. MySQL Stored procedure to Generate-Extract Insert Statement A lot of places I saw people asking for ways...
3. Stored procedure to add-remove prefix by rename table mysql Here is one more procedure – (this time) for mass...

Sometimes we have some special need for a stored procedure to call another to do something. That is fine, but what if the second stored proc failed for some reason? Maybe you want to halt the first stored procedure (the caller) and not proceed with the work until the problem is verified and resolved. So How do you do it?

Simple, get a return code from the called procedure to determine if it worked or not!

Here’s a sample piece of code to explain better:


DROP PROCEDURE IF EXISTS `test`.`testing123`;
DELIMITER $$

CALL `test`.`testing456` ();


testing123 is the second stored proc in this case, called from testing456. The trick is to have an exit handler which returns a status number to the first stored proc (testing456). The latter will hold an if condition to do `something` depending on the return code received by testing123.

If you have any other suggestions I’d appreciate your input.

Lets paint the picture:

Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
Scenario part 2 : Legacy system where the people who wrote it left a long time ago
Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt yourself.

I wonder, who’s gonna cover my health insurance if i go crazy? :)

mysql 02:55:47 DEV > call storedprocblahbla(‘I’,'am’,'going’,'crazy’);
ERROR 1052 (23000): Column ‘state_of_mind’ in field list is ambiguous

Sure thats REALLY REALLY helpful thanks :), you know what, lets just mysqldump -d -R -B db1 db2 db3 db4 db5 > /wherever/you/like and grep for column ‘state_of_mind’.

I love a challenge but facing migration of so many stored procs I’d really love to have something “GOOD” to debug them with.

The solutions I found were:
1. Illatis eclipse plugin for $40
works on linux, mac and windows but does show me a lot of:
“An error has occurred when activating this view
com/illatis/parser/lib/k”
2. mydebugger for $50
works on linux and mac via wine
3. dbForge for MySQL for $50
works only on windows due to .net framework (kinda crap because I `hate` using the former)

any other ideas are very welcome, especially opensource ones but I can’t find anything.

I can always try setting up debugging using GDB but would rather try to avoid that.

One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

First, a simple table, not partitioned (yet):

use test;
DROP TABLE IF EXISTS my_part;
CREATE TABLE IF NOT EXISTS my_part (
  id int NOT NULL,
  creationDate datetime NOT NULL,
  PRIMARY KEY (id,creationDate)
) ENGINE=InnoDB;

In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.

Now, add the partition definition. This can be done in the CREATE statement, but I have found that it is easier for me to think about what fields and indexes I want first, and then worry about partitioning, so I naturally gravitate towards defining the fields and indexes first and then altering the table to add partitioning. This also helps me think about how to modify an existing table to add partitioning.

ALTER TABLE my_part PARTITION BY RANGE (TO_DAYS(creationDate)) (
partition 2010_07_01 values less than (to_days('2010-07-02')),
partition 2010_07_02 values less than (to_days('2010-07-03')),
partition 2010_07_03 values less than (to_days('2010-07-04')),
partition 2010_07_04 values less than (to_days('2010-07-05'))
);

This makes it pretty clear what is happening — the idea is to give the partition names actual dates that they hold, so that it is easy to see what partitions need to be added and deleted.

Deleting partitions

I find that making stored procedures makes things easy….so I will define a procedure called partition_drop to drop partitions. The partition_drop stored procedure takes in a table name and schema name to drop partitions from, and a date to delete up through and including that date. Here’s the procedure:

DELIMITER ||
DROP PROCEDURE IF EXISTS partition_drop ||

CREATE PROCEDURE partition_drop (IN through_date date, IN tbl varchar(64), IN db varchar(64))
BEGIN
DECLARE delete_me varchar(64);
DECLARE notfound BOOL DEFAULT FALSE;
DECLARE pname CURSOR FOR SELECT PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tbl AND TABLE_SCHEMA=db
AND DATE(PARTITION_NAME)!= 0
AND DATE(PARTITION_NAME) IS NOT NULL
AND DATE(PARTITION_NAME)<=through_date;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound:=TRUE;
OPEN pname;

cursor_loop: LOOP
FETCH pname INTO delete_me;
IF notfound THEN LEAVE cursor_loop; END IF;
SET @alter_stmt:=CONCAT("ALTER TABLE ",db,".",tbl," DROP PARTITION ",delete_me);

# sanity check commented out for production use
# SELECT @alter_stmt;
PREPARE stmt_alter FROM @alter_stmt; EXECUTE stmt_alter; DEALLOCATE PREPARE stmt_alter;

END LOOP;
CLOSE pname;
END ||
DELIMITER ;

Go ahead and run CALL partition_drop('2010-07-02','my_part','test'); to verify that SHOW CREATE TABLE my_part; shows that the desired partitions have been dropped.

Adding partitions

Adding partitions is similar to deleting partitions — using a stored procedure that takes in the date to add partitions up to that date. It will not try to add so many partitions that the table will have more than 1024, and it won’t add any partitions that already exist.

DELIMITER ||
DROP PROCEDURE IF EXISTS partition_add ||

CREATE PROCEDURE partition_add (IN through_date date, IN tbl varchar(64), IN db varchar(64))
BEGIN
DECLARE add_me char(10);
DECLARE max_new_parts,add_cnt smallint unsigned default 0;
SELECT 1024-COUNT(*) AS max_new_parts,
SUM(CASE WHEN
 DATE(PARTITION_NAME)>=through_date then 1 else 0
 END)
INTO max_new_parts, add_cnt
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE  TABLE_SCHEMA = db
 AND TABLE_NAME = tbl;

IF add_cnt=0 THEN
BEGIN
SELECT MAX(DATE(PARTITION_NAME)) INTO add_me
FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tbl and TABLE_SCHEMA=db
AND DATE(PARTITION_NAME)<through_date;

# to do: declare handler for exceptions here
IF DATEDIFF(through_date,add_me)+1 < max_new_parts THEN
BEGIN
WHILE add_me<through_date do BEGIN
SET add_me:=DATE_FORMAT(add_me + INTERVAL 1 DAY,"%Y_%m_%d");
SET @alter_stmt:=CONCAT("ALTER TABLE ",db,".",tbl," ADD PARTITION (PARTITION ",add_me," VALUES LESS THAN (TO_DAYS('",add_me+INTERVAL 1 DAY, "')))" );

PREPARE stmt_alter FROM @alter_stmt; EXECUTE stmt_alter; DEALLOCATE PREPARE stmt_alter;

END;
END WHILE;
END;
END IF;
END;
END IF;
END ||
DELIMITER ;

Here’s how to call that stored procedure:
CALL partition_add ('2010_07_10', 'my_part','test');

Caveat: This stored procedure will only add in partitions from the maximum partition name/date until the date you add it in, it will not fill in any gaps. However, it is possible to add in another input parameter to be the “start date” and change the stored procedure to add the partition if it does not exist, from the start date through the end date.

Note: It was pointed out by Roland Bouman that it would be better to change both the add and drop stored procedures to do one ALTER TABLE instead of sequential ones. He wrote:

It’s probably better to generate a single statement to drop / add all partitions.

So the general pattern would be:

- generate and concatenate lines for each partition
- one single sequence of prepare, execute, deallocate to execute one DDL statement.

For the drop partition procedure, this approach would allow you to do away with the cursor. You can simply use GROUP_CONCAT in a single SELECT…INTO statement to generate the entire DDL statement.

Note: Roland also mentioned that these stored procedures could be generalized to use any interval, for example if each table partition held 7 days. The only limitation is that to get the optimal partition performance with dates, use either TO_DAYS(date_field) or YEAR(date_field) as the partitionining function.

Putting it all together

The procedure: daily I would run the following in MySQL, to keep only the previous “x” days:

CALL partition_drop('CURRENT_DATE()-INTERVAL x DAY', 'tbl_name','schema_name');

To add new partitions, I would do:
CALL partition_add('CURRENT_DATE()-INTERVAL 32 DAY', 'tbl_name','schema_name');

It is run daily just in case; the side benefit is that it will catch any gaps if you do not modify the stored procedure to include a start date for when to add. Adding a partition does not take a long time, because there’s no data to reorganize.

I would make a daily MySQL event, and then everything is stored nicely in the database, and backed up with the database. Others may choose to run the stored procedures in a cron script or scheduled task (Windows), but that requires a password to be stored somewhere — either in the script, somewhere the script can read, or in a config file such as .my.cnf.