¿Puedes dividir/explotar un campo en una consulta MySQL?
Tengo que crear un informe sobre las finalizaciones de algunos estudiantes. Cada estudiante pertenece a un cliente. Aquí están las tablas (simplificadas para esta pregunta).
CREATE TABLE `clients` (
`clientId` int(10) unsigned NOT NULL auto_increment,
`clientName` varchar(100) NOT NULL default '',
`courseNames` varchar(255) NOT NULL default ''
)
El courseNamescampo contiene una cadena de nombres de cursos delimitada por comas, por ejemplo, "AB01,AB02,AB03".
CREATE TABLE `clientenrols` (
`clientEnrolId` int(10) unsigned NOT NULL auto_increment,
`studentId` int(10) unsigned NOT NULL default '0',
`courseId` tinyint(3) unsigned NOT NULL default '0'
)
El courseIdcampo aquí es el índice del nombre del curso en el campo client.courseNames . Entonces, si el cliente courseNameses "AB01,AB02,AB03" y el courseIdde la inscripción es 2, entonces el estudiante está en AB03.
¿Hay alguna manera de hacer una única selección en estas tablas que incluya el nombre del curso? Tenga en cuenta que habrá estudiantes de diferentes clientes (y por lo tanto tendrán diferentes nombres de cursos, no todos secuenciales, por ejemplo: "NW01,NW03")
Básicamente, si pudiera dividir ese campo y devolver un solo elemento de la matriz resultante, eso sería lo que estoy buscando. Esto es lo que quiero decir con pseudocódigo mágico:
SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`]
FROM ...
Hasta ahora, quería mantener esas listas separadas por comas en mi base de datos SQL, ¡conociendo todas las advertencias!
Seguí pensando que tienen ventajas sobre las tablas de búsqueda (que proporcionan una forma de obtener una base de datos normalizada). Después de algunos días de negarme, he visto la luz :
- El uso de tablas de búsqueda NO genera más código que esas desagradables operaciones de cadenas cuando se usan valores separados por comas en un campo.
- La tabla de búsqueda permite formatos de números nativos y, por lo tanto, NO es más grande que esos campos csv. Aunque es MÁS PEQUEÑO.
- Las operaciones de cadenas involucradas son escasas en código de lenguaje de alto nivel (SQL y PHP), pero costosas en comparación con el uso de matrices de números enteros.
- Las bases de datos no están destinadas a ser legibles por humanos, y es en gran medida estúpido intentar ceñirse a las estructuras debido a su legibilidad/editabilidad directa, como lo hice yo.
En resumen, hay una razón por la cual no existe una función SPLIT() nativa en MySQL.
La única función de división de cadenas de MySQL es SUBSTRING_INDEX(str, delim, count). Puede utilizar esto para, por ejemplo:
Devuelve el elemento antes del primer separador de una cadena:
mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1); +--------------------------------------------+ | SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1) | +--------------------------------------------+ | foo | +--------------------------------------------+ 1 row in set (0.00 sec)Devuelve el elemento después del último separador de una cadena:
mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1); +---------------------------------------------+ | SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1) | +---------------------------------------------+ | qux | +---------------------------------------------+ 1 row in set (0.00 sec)Devuelve todo lo que está antes del tercer separador en una cadena:
mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3); +--------------------------------------------+ | SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3) | +--------------------------------------------+ | foo#bar#baz | +--------------------------------------------+ 1 row in set (0.00 sec)Devuelve el segundo elemento de una cadena, encadenando dos llamadas:
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1); +----------------------------------------------------------------------+ | SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1) | +----------------------------------------------------------------------+ | bar | +----------------------------------------------------------------------+ 1 row in set (0.00 sec)
En general, una forma sencilla de obtener el enésimo elemento de una #cadena separada (asumiendo que sabes que definitivamente tiene al menos n elementos) es hacer:
SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1);
La SUBSTRING_INDEXllamada interna descarta el enésimo separador y todo lo que sigue, y luego la SUBSTRING_INDEXllamada externa descarta todo excepto el elemento final que queda.
Si desea una solución más sólida que devuelva NULLsi solicita un elemento que no existe (por ejemplo, solicita el quinto elemento de 'a#b#c#d'), entonces puede contar los delimitadores usandoREPLACE y luego regresar condicionalmente NULLusando IF():
IF(
LENGTH(your_string) - LENGTH(REPLACE(your_string, '#', '')) / LENGTH('#') < n - 1,
NULL,
SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1)
)
¡Por supuesto, esto es bastante feo y difícil de entender! Entonces es posible que desees envolverlo en una función:
CREATE FUNCTION split(string TEXT, delimiter TEXT, n INT)
RETURNS TEXT DETERMINISTIC
RETURN IF(
(LENGTH(string) - LENGTH(REPLACE(string, delimiter, ''))) / LENGTH(delimiter) < n - 1,
NULL,
SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, n), delimiter, -1)
);
Luego puedes usar la función como esta:
mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 3);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 3) |
+----------------------------------+
| baz |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 5);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 5) |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SPLIT('foo###bar###baz###qux', '###', 2);
+------------------------------------------+
| SPLIT('foo###bar###baz###qux', '###', 2) |
+------------------------------------------+
| bar |
+------------------------------------------+
1 row in set (0.00 sec)
