Secuencias en Mysql

Mysql no soporta secuencias, asi que basandome en este artículo:
Articulo

podemos emular el funcionamiento:


CREATE TABLE `mysql`.`sequence_data` (
`sequence_name` varchar(100) NOT NULL,
`sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
`sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
`sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
`sequence_cur_value` bigint(20) unsigned DEFAULT 1,
`sequence_cycle` boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (`sequence_name`)
) ENGINE=innodb;

-- Secuencia con valores por defecto
INSERT INTO mysql.sequence_data
(sequence_name)
VALUE
('sq_my_sequence');



-- Secuencia con datos especificados
INSERT INTO mysql.sequence_data
(sequence_name, sequence_increment, sequence_max_value)
VALUE
('sq_sequence_2', 10, 100);


DELIMITER $$

SET GLOBAL log_bin_trust_function_creators = 1 $$
DROP FUNCTION IF EXISTS mysql.nextval $$
CREATE FUNCTION mysql.nextval (`seq_name` varchar(100)) RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
DECLARE cur_val bigint(20);

SELECT
sequence_cur_value INTO cur_val
FROM `mysql`.`sequence_data`
WHERE sequence_name = seq_name;

IF cur_val IS NOT NULL THEN
UPDATE `mysql`.`sequence_data`
SET
sequence_cur_value = IF (
(sequence_cur_value + sequence_increment) > sequence_max_value,
IF (
sequence_cycle = TRUE,
sequence_min_value,
NULL
),
sequence_cur_value + sequence_increment
)
WHERE
sequence_name = seq_name;
END IF;

RETURN cur_val;

END $$

DELIMITER ;


Establecer max_connections "on the fly"


mysql> set @@global.max_connections = 300;

$ mysql --skip-column-names -s -e "SHOW variables like 'max_connections'"
max_connections 300


Tamaño de Tablas en Mysql desde Procedure

A veces se hace tedioso consultar el tamaño de las tablas. Con este procedure hacemos nuestra vida un poco más sencilla (para clientes por consola):


DELIMITER $$

CREATE PROCEDURE `sizes`(table_ VARCHAR(200) )

BEGIN

SELECT table_name,
round((data_length+index_length)/1024/1024,2) as "Size in MB",
round(data_free/1024/1024) as "Free data in MB"
FROM information_schema.`TABLES` T
WHERE table_name REGEXP table_;

END
$$

CALL sizes('client');