mysql-apicultur

Este post, será bastante extraño, pocas explicaciones y bastante contenido. Así que ¡al turrón!

El objetivo de este conjunto de instrucciones para Mysql es convertir un campo de texto en una tabla, en una tabla en la que cada una de las palabras de ese campo sean una fila de la nueva tabla. Por tanto tendremos inicialmente un campo en una fila en una tabla y al final, mantendremos ese campo pero tendremos una tabla con tantos registros por campo de esa tabla como palabras haya en cada uno de los campos de cada fila.

Es decir si la tabla inicial tiene 1.000 registros  con una media de 20 palabras en el campo con el que trabajamos, finalmente tendremos una nueva tabla con 20.000 registros.

¿Para qué sirve esto? en mi caso, seguro que hay una solución más simple, tengo unos campos en los que se habla de países, ya sea con el nombre del país o con la mención de su gentilicio, en cualquier forma, masculino, femenino, singular o plural. Tengo también una tabla con gentilicios y nombres de países y necesito crear una tabla que las conecte, de modo que me diga en cuales de esos registros se habla de un determinado país. Necesitaría hacer algo así como un LIKE con % pero dentro de ese % debería meter el nombre de un campo.

Así que he optado por esta solución un poco pesada de hacer pero que me resuelve el problema, que es de lo que se trata.

Lo primero es crear las tablas necesarias. Podría hacerse sólo con estas tablas, pero yo creo una intermedia para trabajar sin miedo a cargarme nada. Así que necesitamos tres tablas:

  • Tabla inicial, que tiene el campo a dividir en cuestión, este campo lo llamaremos texto.
  • La tabla final que tendrá una palabra por fila y en la que guardaremos un id de la tabla inicial para saber a qué registro corresponde de la tabla inicial corresponde cada registro la tabla final.
  • La tabla intermedia que tendrá, un campo texto, con el valor inicial del campo texto de la tabla inicial, otros dos de igual tamaño que texto, texto1 y texto2, el id de la tabla inicial y el campo palabra que irá a la tabla final.

El proceso esencialmente consiste en ir buscando los espacios en el campo texto1, por ejemplo, cortar por el primer espacio que encuentre y guardar, la parte anterior al espacio, la palabra en el campo palabra y el resto en texto2, luego invertimos el proceso. Se puede hacer más con un sólo campo, pero cuesta mucho ajustarlo así que los dos campos, permiten ver si el proceso va correctamente.

Una vez hecho el corte, copiamos la palabra extraída en la tabla de palabras y seguimos con el proceso. Hay una  serie de comprobaciones adicionales que lo que hacen es solucionar el último caso, es decir cuando no hay más espacios. Según escribo esto se me ocurre que se podría haber solucionado más elegantemente poniendo un espacio en blano al final del cvampo texto. problemas de hacer estas cosas a las 4 de la mañana, pero ahí queda la solución sin espacio final para el que le sirva.

Antes de entrar al código os recomiendo que quiteis del campo de texto caracteres ratos, espacios iniciales y finales y quede todo límpio y con palabras separadas por un espacio. En los enlaces tenéis alguna info de como hacerlo.

Una vez preprado el campo y creadas las tablas que os comentaba, ejecutaremos las siguientes instrucciones en el orden que viene a continuación, repitiendo el proceso hasta que no haya más frases por convertir. Es un poco pesado pero si creáis un script y lo copiais todas las veces que haga falta tendréis que pulsar el botón pocas veces.

Este sería el script:

update intermedia set
texto2=SUBSTRING(texto1,locate(‘ ‘, texto1)+1, length(texto1)+1),
palabra = substring(texto1,1,locate(‘ ‘, texto1)-1)
where 0 < locate (‘ ‘, texto1);

Insert into final (IDInicial, Palabra)
select IDInicial, Palabra from intermedia where Palabra <> “”;

Insert into final (IDInicial, Palabra)
select IDInicial, texto1 from intermedia where texto1 <> “” and 0 = locate(‘ ‘, texto1);

update intermedia set palabra = “” where palabra <> “”;

update intermedia set texto1 = “”, texto2 = “” where texto1 <> “” and 0 = locate(‘ ‘, texto1);

update intermedia set
texto1=SUBSTRING(texto2,locate(‘ ‘, texto2)+1, length(texto2)+1),
palabra = substring(texto2,1,locate(‘ ‘, texto2)-1)
where 0 < locate (‘ ‘, texto2);

Insert into final (IDInicial, Palabra)
select IDInicial, Palabra from intermedia where Palabra <> “”;

Insert into final (IDInicial, Palabra)
select IDInicial, texto2 from intermedia where texto2 <> “” and 0 = locate(‘ ‘, texto2);

update intermedia set palabra = “” where palabra <> “”;

update intermedia set texto2 = “”, texto1 = “” where texto2 <> “” and 0 = locate(‘ ‘, texto2);

Un verdadero lío, verdad, en realidad son dos pasos, algo así como un balancín de 1 a 2 y de 2 a 1. Así que empezamos sólo con una parte del balancín:

update intermedia set
texto2=SUBSTRING(texto1,locate(‘ ‘, texto1)+1, length(texto1)+1),
palabra = substring(texto1,1,locate(‘ ‘, texto1)-1)
where 0 < locate (‘ ‘, texto1);

Insert into final (IDInicial, Palabra)
select IDInicial, Palabra from intermedia where Palabra <> “”;

Insert into final (IDInicial, Palabra)
select IDInicial, texto1 from intermedia where texto1 <> “” and 0 = locate(‘ ‘, texto1);

update intermedia set palabra = “” where palabra <> “”;

update intermedia set texto1 = “”, texto2 = “” where texto1 <> “” and 0 = locate(‘ ‘, texto1);

Algo más claro pero se mantiene el lío, verdad. Hay tres partes, la primera parte:

update intermedia set
texto2=SUBSTRING(texto1,locate(‘ ‘, texto1)+1, length(texto1)+1),
palabra = substring(texto1,1,locate(‘ ‘, texto1)-1)
where 0 < locate (‘ ‘, texto1);

es la que hace la magia, pilla la frase de texto1 y la divide en palabra y lo que queda que guarda en texto2, siempre que haya un espacio en el campo texto1, si no lo hay [ya hemos terminado] no hace nada.

La segunda parte, lo que hace es insertar los valores en la tabla final. Hay dos intrucciones porque una sirve para el caso normal, que la frase siga y la otra para el caso en que sea la última palabra de la frase

Insert into final (IDInicial, Palabra)
select IDInicial, Palabra from intermedia where Palabra <> “”;

Insert into final (IDInicial, Palabra)
select IDInicial, texto1 from intermedia where texto1 <> “” and 0 = locate(‘ ‘, texto1);

La tercera y última fase, limpia la tabla intermedia para seguir con el proceso, en este caso con texto2:

update intermedia set palabra = “” where palabra <> “”;

update intermedia set texto1 = “”, texto2 = “” where texto1 <> “” and 0 = locate(‘ ‘, texto1);

Así que valor y al toro, dudo mucho que esto le sirva a alguien pero al menos a mí me ha solucionado la papeleta.

Una vez que tengáis el conjunto completo de instrucciones, creáis el script y a repetirlo hasta que no haya más trozo de frase por procesar.

Recuerda que en estos entornos no tienes Ctrl-Z.

¡Atención! estas instrucciones son para sistemas de tablas sencillos, y aislados. Tu debes ser el dueño de estos datos para poder hacer todas estas operaciones y además saber muy bien lo que haces. Tu administrador de sistemas te puede matar si haces alguna de estas instrucciones sin su permiso. Recuerda que antes de probar este tipo de operaciones puedes hacer una copia de seguridad de tu tabla en 15 segundos.

Aprovecho para ponerte la estructura de la instrucción UPDATE del manual de MySQL:

Sintaxis para una tabla:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

Sintaxis para múltiples tablas:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

Y aquí la estructura de la instrucción INSERT del manual de MySQL:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

O:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
Share →