comando - MySQL GROUP_CONCAT escapando

mysql separator group_concat

Question

(NOTA: esta pregunta no se trata de escapar de las consultas, se trata de escapar de los resultados)

Estoy usando GROUP_CONCAT para combinar múltiples filas en una lista delimitada por comas. Por ejemplo, supongamos que tengo las dos tablas (ejemplo):

CREATE TABLE IF NOT EXISTS `Comment` (
`id` int(11) unsigned NOT NULL auto_increment,
`post_id` int(11) unsigned NOT NULL,
`name` varchar(255) collate utf8_unicode_ci NOT NULL,
`comment` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
INSERT INTO `Comment` (`id`, `post_id`, `name`, `comment`) VALUES
(1, 1, ''bill'', ''some comment''),
(2, 1, ''john'', ''another comment''),
(3, 2, ''bill'', ''blah''),
(4, 3, ''john'', ''asdf''),
(5, 4, ''x'', ''asdf'');
CREATE TABLE IF NOT EXISTS `Post` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;
INSERT INTO `Post` (`id`, `title`) VALUES
(1, ''first post''),
(2, ''second post''),
(3, ''third post''),
(4, ''fourth post''),
(5, ''fifth post''),
(6, ''sixth post'');

Y quiero enumerar todas las publicaciones junto con una lista de cada nombre de usuario que comentó la publicación:

SELECT
Post.id as post_id, Post.title as title, GROUP_CONCAT(name)
FROM Post
LEFT JOIN Comment on Comment.post_id = Post.id
GROUP BY Post.id

me da:

id title GROUP_CONCAT( name )
1 first post bill,john
2 second post bill
3 third post john
4 fourth post x
5 fifth post NULL
6 sixth post NULL

Esto funciona muy bien, excepto que si un nombre de usuario contiene una coma arruinará la lista de usuarios. ¿MySQL tiene una función que me permita escapar de estos personajes? (Por favor, suponga que los nombres de usuario pueden contener cualquier carácter, ya que esto es solo un esquema de ejemplo)

Answer 1

Lo que Nick dijo realmente, con una mejora, el separador también puede tener más de un personaje.

A menudo he usado

GROUP_CONCAT(name SEPARATOR ''"|"'')

Posibilidades de un nombre de usuario que contenga "|" son bastante bajos, diría.

Answer 2

REPLACE()

Ejemplo:

... GROUP_CONCAT(REPLACE(name, '','', ''//,'')) 

Tenga en cuenta que debe usar una doble barra diagonal inversa (si escapa de la coma con barra inclinada invertida) porque la barra diagonal inversa es mágica, y /, convierte en simple,.

Answer 3

Jason S: Este es exactamente el problema con el que estoy tratando. Estoy usando un framework MVC de PHP y estaba procesando los resultados como usted describe (múltiples filas por resultado y código para agrupar los resultados). Sin embargo, he estado trabajando en dos funciones para implementar en mis modelos. Uno devuelve una lista de todos los campos necesarios para recrear el objeto y el otro es una función que da una fila con los campos de la primera función, crea una instancia de un nuevo objeto. Esto me permite solicitar una fila de la base de datos y convertirla fácilmente en el objeto sin conocer las partes internas de los datos que necesita el modelo. Esto no funciona tan bien cuando varias filas representan un objeto, por lo que estaba tratando de usar GROUP_CONCAT para evitar ese problema.

Answer 4

Si va a hacer la decodificación en su aplicación, tal vez solo use hex :

SELECT GROUP_CONCAT(HEX(foo)) ...

o también puedes poner la longitud en ellos:

SELECT GROUP_CONCAT(CONCAT(LENGTH(foo), '':'', foo)) ...

No es que lo haya probado :-D

Answer 5

Te estás metiendo en esa zona gris donde sería mejor posprocesar esto fuera del mundo de SQL.

Al menos eso es lo que haría: simplemente ORDER BY en lugar de GROUP BY, y recorrer los resultados para manejar la agrupación como un filtro hecho en el idioma del cliente:

1. Comience por inicializar last_id a NULL
2. Obtenga la siguiente fila del resultado (si no hay más filas vaya al paso 6)

3. Si la identificación de la fila es diferente de last_id inicie una nueva fila de salida:

   a. si last_id no es NULL, last_id la fila agrupada

   segundo. establezca la nueva fila agrupada = la fila de entrada, pero almacene el nombre como una única matriz de elementos

   do. establecer last_id al valor de la ID actual

4. De lo contrario (id es lo mismo que last_id ) last_id el nombre de la fila a la fila agrupada existente.

5. Regresa al paso 2
6. De lo contrario, has terminado; si el last_id no es NULL, last_id la fila del grupo existente.

Luego, su salida termina incluyendo nombres organizados como una matriz y puede decidir cómo quiere manejar / escapar / formatearlos.

¿Qué idioma / sistema estás usando? PHP? Perl? ¿Java?

Answer 6

Ahora estoy permitiendo cualquier personaje. Me doy cuenta de que es poco probable que aparezca una pipa, pero me gustaría permitirlo.

¿Qué tal un personaje de control, que debería estar eliminando de la entrada de la aplicación de todos modos? Dudo que necesites, por ejemplo. una pestaña o una nueva línea en un campo de nombre.

Answer 7

Si hay algún otro carácter que sea ilegal en los nombres de usuario, puede especificar un carácter separador diferente utilizando una sintaxis poco conocida:

...GROUP_CONCAT(name SEPARATOR ''|'')...

... ¿Quieres permitir las tuberías? o cualquier personaje?

Escape del carácter separador, tal vez con barra diagonal inversa, pero antes de hacerlo, escape las barras invertidas:

group_concat(replace(replace(name, ''//', ''////'), ''|'', ''//|'') SEPARATOR ''|'')

Esta voluntad:

1. escapar de cualquier barra invertida con otra barra invertida
2. escapar del carácter separador con una barra invertida
3. Concatenar los resultados con el carácter separador

Para obtener los resultados no escaneados, haga lo mismo en el orden inverso:

1. divide los resultados por el carácter separador donde no va precedido de una barra invertida. En realidad, es un poco complicado, quieres dividirlo donde no esté precedido por un número impar de barras negras. Esta expresión regular coincidirá con eso:
   (?<!//)(?:////)*/|
2. reemplace todos los caracteres separados del separador por literales, es decir, reemplace / | con |
3. reemplace todas las barras diagonales inversas dobles por barras inclinadas inversas, por ejemplo, reemplace // con /

Answer 8

Sugeriría GROUP_CONCAT (nombre SEPARATOR ''/ n''), ya que / n generalmente no ocurre. Esto podría ser un poco más simple, ya que no necesita escapar de nada, pero podría ocasionar problemas inesperados. La codificación de decodificación / regexp como lo propuso nick es, por supuesto, muy agradable.

Answer 9

En realidad, hay ascii control characters específicamente diseñados para separar campos y registros de bases de datos:

0x1F (31): unit (fields) separator
0x1E (30): record separator
0x1D (29): group separator

Leer más: sobre personajes ascii

Nunca los tendrá en los nombres de usuario y probablemente nunca en otros non-binary data en su base de datos para que puedan ser utilizados con seguridad:

GROUP_CONCAT(foo SEPARATOR 0x1D)

Luego divida por CHAR(0x1D) en el idioma del cliente que desee.

Answer 10

Solo para ampliar algunas de las respuestas, implementé la segunda sugerencia de @derobert en PHP y funciona bien. Dado MySQL como:

GROUP_CONCAT(CONCAT(LENGTH(field), '':'', field) SEPARATOR '''') AS fields

Usé la siguiente función para dividirla:

function concat_split( $str ) {
// Need to guard against PHP''s stupid multibyte string function overloading.
static $mb_overload_string = null;
if ( null === $mb_overload_string ) {
$mb_overload_string = defined( ''MB_OVERLOAD_STRING'' )
&& ( ini_get( ''mbstring.func_overload'' ) & MB_OVERLOAD_STRING );
}
if ( $mb_overload_string ) {
$mb_internal_encoding = mb_internal_encoding();
mb_internal_encoding( ''8bit'' );
}
$ret = array();
for ( $offset = 0; $colon = strpos( $str, '':'', $offset ); $offset = $colon + 1 + $len ) {
$len = intval( substr( $str, $offset, $colon ) );
$ret[] = substr( $str, $colon + 1, $len );
}
if ( $mb_overload_string ) {
mb_internal_encoding( $mb_internal_encoding );
}
return $ret;
}

Inicialmente también implementé la sugerencia de @ ʞɔıu, usando uno de los separadores de @Lemon Juice. Funcionó bien, pero aparte de su complicación, fue más lento, el principal problema es que PCRE solo permite mirar fijamente hacia atrás, por lo que usar la expresión regular sugerida para dividir requiere capturar los delimitadores; de lo contrario, se perderán las barras diagonales inversas al final de las cadenas. Así que, dado MySQL como (nota 4 PHP barras diagonales => 2 barras diagonales de MySQL => 1 barra invertida real):

GROUP_CONCAT(REPLACE(REPLACE(field, ''////', ''////////'),
CHAR(31), CONCAT(''////', CHAR(31))) SEPARATOR 0x1f) AS fields

la función de división fue:

function concat_split( $str ) {
$ret = array();
// 4 PHP backslashes => 2 PCRE backslashes => 1 real backslash.
$strs = preg_split( ''/(?<!////)((?:////////)*+/x1f)/'', $str, -1, PREG_SPLIT_DELIM_CAPTURE );
// Need to add back any captured double backslashes.
for ( $i = 0, $cnt = count( $strs ); $i < $cnt; $i += 2 ) {
$ret[] = isset( $strs[ $i + 1 ] ) ? ( $strs[ $i ] . substr( $strs[ $i + 1 ], 0, -1 ) ) : $strs[ $i ];
}
return str_replace( array( "///x1f", "////" ), array( "/x1f", "//" ), $ret );
}