Correlativos alfanuméricos en MySQL

[Irwin Rodriguez]

Saludos a Todos y Feliz Inicio de Semana!

Quiero compartir con ustedes una rutina que genera un código consecutivo alfanumérico de cualquier tabla que se pase por parametro:

Esto lo pueden usar para generar códigos de referencia por ejemplo para tablas de clientes o catalogos de productos, también si quieren lo pueden usar como generador de códigos unicos ya que es un procedimiento almacenado pero para eso existen los ID's asi que queda a su criterio.

El código es el siguiente:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `generar_codigo`$$

CREATE PROCEDURE `generar_codigo`(IN p_tabla VARCHAR(20),IN p_campo VARCHAR(20),IN p_codigo VARCHAR(20), IN p_ancho INT(20), IN p_valida INT(2), OUT result VARCHAR(250))
BEGIN
  DECLARE cadalf VARCHAR(20) DEFAULT '';
  DECLARE valnum TINYINT(2) DEFAULT 0;
  DECLARE pos TINYINT(2) DEFAULT 0;
  DECLARE bien BOOLEAN DEFAULT FALSE;
  DECLARE tope INTEGER(20) DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET @retorno = NULL;
  /*Verifico si el código existe en base de datos*/
  SET @query = CONCAT("select ",p_campo," into @retorno from ", p_tabla, " where ",p_campo," = '",p_codigo,"' limit 1");     
  PREPARE stmt1 FROM @query;
  EXECUTE stmt1;
  SET result = @retorno;
  DEALLOCATE PREPARE stmt1;
  SET result := IFNULL(result,'');
  IF p_valida = 1 THEN
    /*Si el código existe entonces se retorna -1*/   
    SET result := IF(result <> '',-1,result);
  ELSE
    IF LENGTH(p_codigo) = p_ancho THEN
    SET result := IF(result <> '',-1,result);
    ELSE
    WHILE pos <= p_ancho DO      
       SET pos := pos + 1;
       SET cadalf := CONCAT(cadalf,SUBSTRING(p_codigo,pos,1));      
    END WHILE;
    SET p_codigo := '';
    SET tope := CONVERT(REPEAT('9', p_ancho - LENGTH(TRIM(cadalf))),UNSIGNED INTEGER);
    WHILE (bien = FALSE) AND (valnum <= tope) DO
       SET valnum := valnum + 1;
       SET p_codigo := CONCAT(TRIM(cadalf),LPAD(TRIM(CONVERT(valnum,CHAR)),p_ancho-LENGTH(TRIM(cadalf)),'0'));
       /*Verifico si el código existe en base de datos*/
       SET @query2 = CONCAT("select ",p_campo," into @retorno from ", p_tabla, " where ",TRIM(p_campo)," = '",TRIM(p_codigo),"' limit 1");             
       PREPARE stmt2 FROM @query2;
       EXECUTE stmt2;
       SET result = @retorno;
       DEALLOCATE PREPARE stmt2;
       SET result := IFNULL(result,'');
       SET bien := IF(result = '',TRUE,FALSE);
    END WHILE;
    IF bien = FALSE THEN
       SET result = -2;
       SET p_codigo := '';
    ELSE
       SET result = p_codigo;
    END IF;
    END IF;
  END IF;
END$$
DELIMITER ;

*******************************************************************************

PARAMETROS:

NOMBRE   FLUJO    TIPO       LONGITUD
p_tabla       entrada   varchar         20
p_campo     entrada   varchar        20
p_codigo     entrada   varchar        20
p_ancho      entrada   varchar        20
p_valida       entrada  int               2
result           salida    varchar       250

P_TABLA => Nombre de la tabla a procesar.

P_CAMPO=> Campo a generar el código (DEBE SER VARCHAR O CHAR)

P_CODIGO => Este tiene varias funciones.

1. Si quieren que la rutina genere un código consecutivo nuevo entonces deben dejarlo en blanco.

2. Si quieren enviar un código y verificar si existe en la tabla entonces pasen el código y lleven el parametro P_VALIDA a 1.

3. Si quieren generar un correlativo con un PREFIJO entonces pasen el prefijo solamente: ejemplo: CLI para que devuelva CLI001 por ejemplo.

P_ANCHO => Ancho del código a generar. No debe ser mayor que el ancho del campo en la tabla. El código resultante será un ZEROFILL.

P_VALIDA => Solo para validar si existe algún código en la tabla. DEBE ENVIARSE EN 1 Y LLENARSE P_CODIGO. Si quieren solamente generar el código entonces lleven a cero el parametro.

RESULT => retorna el código resultante o -1 si el código ya existe en la tabla (aplica solo para P_VALIDA = 1)

USO DEL PROCEDIMIENTO:

Un ejemplo desde la consola sería el siguiente:

1. CALL generar_codigo('cliente','codcli',' ',6,0,@result);
2. SELECT @result AS resultado;

1. Realizamos la llamada al procedimiento pasando sus parámetros. Para este ejemplo solo quiero que me genere el próximo correlativo de la tabla cliente. El campo se llama CODCLI y es VARCHAR(6)

2. Muestro en pantalla el resultado del procedimiento. el resultado puede ser por ejemplo 000001

Si quiero generar correlativos con PREFIJOS entonces:

1. CALL generar_codigo('cliente','codcli','CLI',6,0,@result);

El resultado sería CLI001 por ejemplo.

********************************************************************************

Eso sería todo colegas!

Saludos y espero que les sea de utilidad.

--

Ing. Irwin Rodríguez

 

Consultor Informático

+584125210679

 

"Las ideas son fuerzas intangibles, pero tienen más poder que el cerebro físico de donde nacen. Tienen el poder de seguir viviendo, aún después de que el cerebro que las ha creado haya regresado al polvo"

NOTA: La información mostrada en este mensaje es de caracter Confidencial y está dirigida unicamente a los contactos señalados en el encabezado; si el lector de este correo no es el destinatario del mismo, se le notifica que cualquier copia o distribución queda totalmente prohibida. Si usted ha recibido este mensaje por error, por favor notifique inmediatamente al remitente por este mismo medio y bórrelo de su sistema.

[Carlos Miguel FARIAS]

Interesante propuesta.

Que ventaja tiene este tipo de indice sobre los auto-incrementales de provee el mysql, que andan muy bien, y hasta se pueden restaurar (p.e. inicializar de cero)?

Por lo que entiendo del algoritmo, para encontrar un código, empieza siempre de un pos = 0, y luego genera claves y busca si está en la base. Entonces...

a) Si la tabla es grande, no se hace muy lento buscar uno por uno los consecutivos?

b) Si se borro un registro intermedio, por ejemplo, CLI0321, pero ya tienes generado hasta CLI0335, no vuelve a generarte CLI0321? En ese caso, dejaría de ser consecutivo, y podría además generar conflicto con claves con igual valor en otro lugar de la tabla.

c) Si tienes concurrencia desde varios usuarios, y todos piden una clave nueva. Creo que pueden generarse claves duplicadas, porque evidentemente, el SGBD va a procesar posiblemente cada corrida del almacenado por separado (a la par) y por ende, generar claves iguales.

d) El procedimiento no veo que asegure que una clave generada, no vuelva a usarse, o sea, genera una clave, pero no congela esa clave, solo detecta si se uso antes, pero la aplicación puede tardar en aplicarla y se hace un desmadre.

Al menos esas son las cosas que creo que no funcionarían (ojo, no hice pruebas más allá del seguimiento del código).

En mi caso, uso para generar consecutivos que además contengan información de auditoria, y la duplicidad es casi imposible (salvo que un mismo usuario genere más de 1000 registros por segundo para la misma tabla).

Ese algoritmo ya lo he posteado varias veces en varios foros de fox.

Saludos: Miguel, La Pampa (RA)

[Irwin Rodriguez]

Hola Carlos,

Todas tus preguntas son válidas y tienes razón en cada una de ellas, este procedimiento me lo pidió un cliente (desarrollador) que lo tenia en un PRG orientado a DBF's y lo quería llevar a un SP en MySQL. Yo le realicé preguntas muy similares pero el insistia en dejarlo tal cual porque asi lo estaban trabajando desde hace ya varios años.

Al terminarlo me gustó como funcionaba y por eso quise compartirlo pero pienso que se puede mejorar bastante para dejarlo mas robusto. Los códigos "reutilizables" (como lo llaman ellos) me sorprendieron un poco porque en teoría eso es una mala praxis pero en fin... como dice el dicho "cada loco con su tema".

Saludos!

[Carlos Miguel FARIAS]

Creo que la misma funcionalidad se puede lograr, pero se puede mejorar mucho el algoritmo.

O sea, en lugar de buscar cada consecutivo por grupo, buscar el máximo y a partir de alli incrementar

SELECT MAX(campo_clave) FROM laTabla WHERE campo_clave LIKE CONCAT(p_codigo, "%")

O sea, buscas directamente el último cargado, luego, extraes la parte numérica, la incrementas y ya tenes el nuevo código.

Es una idea. Pero así como está, si no han tenido conflicto de claves duplicadas, es porque la candencia de carga es baja (no tienen concurrencia).

Saludos: Miguel, La Pampa (RA)

[Irwin Rodriguez]

Exacto, la estaré modificando porque casualmente me sirve para un aplicativo que tengo en la web.

No les da problemas porque es una data "pequeña" y de seguro no tienen muchas concurrencias.

Saludos!

[Carlos Miguel FARIAS]

Si quieres, fíjate lo que propuse hace un tiempo en el foro sobre autoincrementales que proveen datos de auditoria.

Puede que te sea más útil que esto otro, sobre todo si no tienen que ser gestionados por humanos ;-D

Si no lo encuentras, te lo paso, es una formula de prácticamente una sola línea (en VFP)

Saludos: Miguel, La Pampa (RA)

[Irwin Rodriguez]

Estuve buscando un poco pero no lo conseguí, si puedes me lo pasas por aca.

Saludos!

[Carlos Miguel FARIAS]

Fíjate si será simple que lo tengo definido como una constante.

#DEFINE IDENTIFICADOR_UNIVERSAL DEFAULT NTOM( VAL( STRTRAN( SYS( 2007, SYS( 0 ) ) + STR( VAL( SYS( 1 ) ) - 2450000, 5, 0 ) + STR( SECONDS( ), 9, 4 ), " ", "0" ) ) )

Cuando creo una tabla (lo hago por código con SQL Create) la clave principal es un campo monetario (8 bytes en disco, 8 bytes en entrada en la clave, por ser numérica).

CREATE myTabla ( idTabla Y PRIMARY KEY IDENTIFICADOR_UNIVERSAL, etc

O podes crear una función

FUNCTION IdentificadorUniversal()

   RETURN NTOM( VAL( STRTRAN( SYS( 2007, SYS( 0 ) ) + STR( VAL( SYS( 1 ) ) - 2450000, 5, 0 ) + STR( SECONDS( ), 9, 4 ), " ", "0" ) ) )

ENDFUNC

Donde:

   SYS(0) devuelve máquina y usuario.

   SYS(1) devuelve el dia juliano (número de día a partir de una fecha - fijate en la ayuda)

   Con SYS(2007, convierto la string en un hash de longitud fija, es mucho más corto e irrepetible (distinto para distinta máquina o usuario), bueno la probabilidad es muy baja.

   A la fecha juliana, le resto la constante que figura, para que me queden números de longitud fija, que restringen el rango de fechas a pocos siglos (o sea para 2100, la rutina no andaría, pero pienso estar jubilado ;-D, es más si estoy, seré ininputable + ;-DD)

   A eso le agrego, la hora del día en segundos y milisegundos, reemplazo los blancos intermedios que puedan haber quedado por ceros y lo paso luego a numérico y luego a monetario.

   O sea que, la clave es diferente por máquina, usuario, aunque se genere en el mismo instante.

   En el único caso de conflicto que pude detectar, fue generando claves masivas (en un proceso por lotes) con mas de 1000 registros por segundo, desde una misma máquina por el mismo usuario.

   Lo interesante de esta clave es que todos los elementos que se concatenaron, quedan en posiciones fijas. O sea que si luego conviertes el valor en string y la fraccionas con SUBSTR, recuperas el hash de maquina-usuario, el trozo de dia juliano, que normalizas agregando la constante que se resto y en la última parte la hora en segundos y milisegundos.

   Si en una tabla guardas todas los hash de máquina-usuario asociado con los nombres correspondientes, puedes dilucidar para auditoria: Where, Who, When, se creó el registro (what) que son las 4 W de auditoria, Donde, Quien, Cuando y Que.

   Cuando se modifica un registro con datos críticos, puedo nuevamente usar la función para registrar esos datos, en una columna de último cambio.

   Adaptarlo a diferentes SGBD no lo veo dificultoso, en esos casos, el campo destino será un NUMERIC o DECIMAL con 4 decimales (el tamaño total unos 18 dígitos), creo que SQL Server también hay campos monetarios.

   El orden de armado de la clave se puede alterar, pero si pones adelante si problema, poniendo adelante la parte que corresponde a el día, pero pasar adelante los segundos, tienes que adaptar el tema de los decimales (podrías usar un big integer y listo).

   La ventaja que le veo a esto sobre un autoincremental, es que el autoincremental si me provee unicidad de clave primaria, pero solo sirve para eso. Con lo que planteo, guardo un varios datos interesantes que además de unicidad, me está proveyendo quien cuando donde creo el registro, y la misma función, me provee algo similar, al momento de los cambios.

   Además, para el "ajeno" que mira la tabla, se encuentra con un número "extraño" que si quiere manosear, si no sabe el algoritmo, puede generar una inconsistencia significativa.

   Eso si, un mismo usuario en una misma máquina puede generar a lo sumo 86,4 MILLONES de registros en la MISMA tabla.

   Nada es perfecto.

Cualquier consulta o sugerencia previo pago de más sonrisas como la de la foto.

[Irwin Rodriguez]

Hola Carlos,

Muy interesante tu rutina.

Yo hice algunos ajustes para mejorar la mia tomando tu sugerencia y asi me queda:

DELIMITER $$

USE `seincapru`$$

DROP PROCEDURE IF EXISTS `p_codcoralf`$$

CREATE PROCEDURE `genera_codigo`(IN p_tabla VARCHAR(20),IN p_campo VARCHAR(20),IN p_codigo VARCHAR(20), IN p_ancho INT(20), IN valida INT(2), OUT result VARCHAR(250))

BEGIN
          DECLARE cadalf VARCHAR(20) DEFAULT '';
          DECLARE valnum TINYINT(2) DEFAULT 0;
          DECLARE pos TINYINT(2) DEFAULT 0;
          DECLARE bien BOOLEAN DEFAULT FALSE;
          DECLARE tope INTEGER(20) DEFAULT 0;

          DECLARE cadres VARCHAR(20) DEFAULT '';
          DECLARE poscadact VARCHAR(1) DEFAULT '';

          DECLARE CONTINUE HANDLER FOR NOT FOUND SET @retorno = NULL;
          /*Verifico si el código existe en base de datos*/
          SET @query = CONCAT("select ",p_campo," into @retorno from ", p_tabla, " where ",p_campo," = '",p_codigo,"' limit 1");     
          PREPARE stmt1 FROM @query;
          EXECUTE stmt1;
          SET result = @retorno;
          DEALLOCATE PREPARE stmt1;
          SET result := IFNULL(result,'');

          IF valida = 1 THEN

            /*Si el código existe entonces se retorna -1*/   
            SET result := IF(result <> '',-1,result);
          ELSE
            IF LENGTH(p_codigo) = p_ancho THEN
        SET result := IF(result <> '',-1,result);
            ELSE

            SET @query2 = CONCAT("select MAX(",p_campo,") into @retorno from ", p_tabla, " where ",TRIM(p_campo)," like '",TRIM(p_codigo),"%' limit 1");

            PREPARE stmt2 FROM @query2;
            EXECUTE stmt2;
            SET result = @retorno;           
            DEALLOCATE PREPARE stmt2;
            SET result := IFNULL(result,'');

            IF result = '' THEN
            SET cadalf := '1';
            IF p_codigo <> '' THEN
               SET result := LPAD(cadalf, p_ancho - LENGTH(TRIM(p_codigo)), '0');
               SET result := CONCAT(p_codigo,result);
            ELSE
               SET result := LPAD(cadalf, p_ancho, '0');
            END IF;
            ELSE
               SET pos := 1;

               WHILE pos <= p_ancho DO

              SET poscadact := SUBSTRING(result,pos,1);
              IF poscadact IN('0','1','2','3','4','5','6','7','8','9') THEN
                 SET cadres := CONCAT(cadres, poscadact);
              END IF;

              SET pos := pos + 1;

               END WHILE;
               SET result := CONVERT(cadres,UNSIGNED INTEGER) + 1;
               SET result := LPAD(CONVERT(result,CHAR),p_ancho - LENGTH(TRIM(p_codigo)), '0');
               SET result := CONCAT(p_codigo,result);

            END IF;
            END IF;
          END IF;
    END$$

DELIMITER ;

/*******************************************************************************************************************/

Trabaja super rapido y funciona igual con PREFIJOS, ah y ya no "REUTILIZA" los códigos :-)

La anterior la probé con una tabla de 40.000 mil registros y pues tardó como 20 minutos.

saludos!

[Carlos Miguel FARIAS]

En cuanto mejoro?

[MALKASOFT ADPI: http://www.developervfp.blogspot.com/]

Hola yo lo que hago es algo sencillo y la verdad no se demora nada puedes probar con la cantidad de registros que quieres y también si quieres le puedes poner en un procedure.

SELECT COALESCE(MAX(NumeroDoc),0)+1 AS Numero FROM Tutabla WHERE Tienda='01' AND Documento='FA'

Estando en VFP puedes hacer en un Padl no lo he trabajado mucho ya que no hay mas que hacer

cNumero = Padl(Numero,10,'0')

Saludos; 

Ing. Russvell Jesus Soto Gamarra 

Canal YouTube: http://www.youtube.com/rycjesusrj

Blog: http://developervfp.blogspot.mx/

Framework Multi-conexion v6.0 trabaja cualquier base de datos

(SQLServer, MySQL, Firebird, MariaDB, PostgreSQL, Oracle y etc.) 

http://www.youtube.com/watch?v=PCCZvCaet74

[Irwin Rodriguez]

Pues la mejora fue increible. De 20 minutos aprox. pasó a menos de un segundo, estoy probando con tablas mas gordas y no pasa del segundo.