Estoy aprendiendo Node JS usando MySQL como base de datos back-end en un servidor Linux y encontré algo que no entiendo y todo está relacionado con MySQL.
Tengo la siguiente tabla con una fila de datos. El id de esta fila es 6
CREATE TABLE `posts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `authorID` int(11) DEFAULT NULL, `title` varchar(90) COLLATE utf8_bin DEFAULT NULL, `body` mediumtext COLLATE utf8_bin, `createdDate` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Entonces, la siguiente consulta funciona bien y devuelve una fila en la tabla.
SELECT P.id,U.userName as `authorName`,U.userEmail as `authorEmail`,P.title,P.body,P.createdDate FROM posts P LEFt JOIN users U ON P.authorID = U.id WHERE P.id = '6'
Esta otra consulta también funciona (no devuelve datos):
SELECT P.id,U.userName as `authorName`,U.userEmail as `authorEmail`,P.title,P.body,P.createdDate FROM posts P LEFt JOIN users U ON P.authorID = U.id WHERE P.id = '60'
Pero...! La siguiente consulta es donde estoy perdido (¡devuelve la fila con ID 6!):
SELECT P.id,U.userName as `authorName`,U.userEmail as `authorEmail`,P.title,P.body,P.createdDate FROM posts P LEFt JOIN users U ON P.authorID = U.id WHERE P.id = '6F'
¿Por qué? Estoy pasando un 6F literal dentro de comillas simples (para asegurarme) y no debería devolver nada. No hay ninguna publicación con ID 6F. Por cierto, puedo sustituir 6F con 6lo que sea y el problema persistirá. Sin embargo, F6 no devolverá datos como se esperaba.
¿Por qué es esto? ¿Es porque F es un carácter y MySQL espera un número entero y, por lo tanto, trunca cualquier cosa que no sea parte del número entero?
Y lo que es más importante, ¿cómo puedo eliminar esta ambigüedad?
Como se explica en el Manual de referencia de MySQL 8.0 , §12.2 "Conversión de tipos en la evaluación de expresiones" :
Cuando se utiliza un operador con operandos de diferentes tipos, se produce una conversión de tipos para que los operandos sean compatibles. Algunas conversiones ocurren implícitamente. […]
y:
Las siguientes reglas describen cómo se produce la conversión para las operaciones de comparación:
- […]
- En todos los demás casos, los argumentos se comparan como números de coma flotante (reales). Por ejemplo, una comparación de operandos numéricos y de cadena tiene lugar como una comparación de números de coma flotante.
Entonces, su número entero 6
y la cadena '6F'
se están convirtiendo al número real 6.0
, y se comparan igual.
Para solucionar esto, se me ocurren dos opciones:
'6'
o '6F'
. . . no. No hay razón para que su aplicación busque usando el tipo de datos incorrecto.CAST(P.id AS CHAR) = '6'
[ enlace ]. Pero creo que esto puede tener un impacto significativo en el rendimiento, porque creo que esto podría obligar a MySQL a escanear todas las filas para encontrar aquellas en las que CAST(P.id AS CHAR)
se evalúa con el resultado correcto. (Pero, realmente no estoy seguro. Tal vez el optimizador sea lo suficientemente inteligente como para darse cuenta de que CAST(P.id AS CHAR) = '6'
es equivalente a P.id = 6
y que CAST(P.id AS CHAR) = '6F'
siempre es falso.)Tiene razón en su suposición: MySQL tratará una cadena que conduce a un número entero como si fuera un número entero y analizará cualquiera de los valores 'Char' de su cadena. Si su columna es de tipo Integer, intentará convertir su cadena literal.
Para remediar esto, puede usar la función CAST
para asegurarse de que no haya ambigüedad:
SELECT * FROM user WHERE CAST(user.id AS CHAR(9)) = '1F';
Sin embargo, estrictamente hablando, su sistema nunca debería pasar 1F
a un campo de ID, ya que su campo de ID debe ser un número entero (con el entendimiento de que el error del usuario o la entrada deben manejarse antes de llegar a la consulta)
Si desea obtener todas las ID que comienzan con 6, intente hacer:
WHERE Cast(P.id as varchar(20)) LIKE '6%'