primera - ¿Cómo se escribe una consulta que no distingue entre mayúsculas y minúsculas tanto para MySQL como para Postgres?
sql pasar a mayuscula (11)
Estoy ejecutando una base de datos MySQL localmente para el desarrollo, pero implementando en Heroku que usa Postgres. Heroku maneja casi todo, pero mis sentencias Like insensibles a mayúsculas y minúsculas se vuelven sensibles a las mayúsculas y minúsculas. Podría usar sentencias de iLike, pero mi base de datos MySQL local no puede manejar eso.
¿Cuál es la mejor manera de escribir una consulta que no distinga entre mayúsculas y minúsculas y que sea compatible tanto con MySQL como con Postgres? ¿O debo escribir separadamente las declaraciones Like e iLike dependiendo de la base de datos con la que mi aplicación está hablando?
En postgres, puedes hacer esto:
SELECT whatever FROM mytable WHERE something ILIKE ''match this'';
No estoy seguro de si hay un equivalente para MySQL, pero siempre puedes hacer esto, que es un poco feo, pero debería funcionar tanto en MySQL como en postgres:
SELECT whatever FROM mytable WHERE UPPER(something) = UPPER(''match this'');
Hay varias respuestas, ninguna de las cuales es muy satisfactoria.
- LOWER (bar) = LOWER (?) Funcionará en MySQL y Postgres, pero es probable que funcione terriblemente en MySQL : MySQL no usará sus índices debido a la función LOWER. En Postgres puede agregar un índice funcional (en LOWER (bar) ) pero MySQL no lo admite.
- MySQL hará (a menos que haya establecido una collation sensible a las mayúsculas y minúsculas) hacer concordancia de mayúsculas y minúsculas de manera automática y usar sus índices. ( bar =? ).
- Desde su código fuera de la base de datos, mantenga los campos bar y bar_lower , donde bar_lower contiene el resultado de lower (bar) . (Esto también puede ser posible usando desencadenadores de base de datos). (Vea una discusión de esta solución en Drupal ). Esto es torpe, pero al menos se ejecuta de la misma manera en casi todas las bases de datos.
La conversión a superior es lo mejor, ya que cubre la sintaxis compatible para los 3 servidores de bases de datos de Rails más utilizados. PostgreSQL, MySQL y SQLite admiten esta sintaxis. Tiene el inconveniente (menor) de que tiene que escribir en mayúsculas su cadena de búsqueda en su aplicación o en su cadena de condiciones, lo que lo hace un poco más feo, pero creo que la compatibilidad que gana lo hace valioso.
Tanto MySQL como SQLite3 tienen un operador LIKE insensible a mayúsculas y minúsculas. Solo PostgreSQL tiene un operador LIKE sensible a las mayúsculas y minúsculas y un operador ILIKE específico de PostgreSQL (por el manual) para búsquedas que no distinguen entre mayúsculas y minúsculas. Puede especificar ILIKE insead de LIKE en sus condiciones en la aplicación Rails, pero tenga en cuenta que la aplicación dejará de funcionar en MySQL o SQLite.
Una tercera opción podría ser verificar qué motor de base de datos está utilizando y modificar la cadena de búsqueda en consecuencia. Esto se puede hacer mejor pirateando / pausando los adaptadores de conexión de ActiveRecord y haciendo que el adaptador PostgreSQL modifique la cadena de consulta para sustituir "LIKE" por "ILIKE" antes de la ejecución de la consulta. Esta solución es, sin embargo, la más intrincada y, a la luz de formas más sencillas, como aplicar mayúsculas a ambos términos, creo que este no es el esfuerzo (aunque obtendría muchos puntos de brownie por hacerlo de esta manera).
La moraleja de esta historia es: no utilice una pila de software diferente para el desarrollo y la producción. Nunca.
Acabarás con errores que no puedes reproducir en dev; su prueba no tendrá valor. Simplemente no lo hagas.
El uso de un motor de base de datos diferente está fuera de discusión: habrá MUCHO más casos en que se comporte de manera diferente a SIMPLE (también, ¿ha revisado las intercalaciones en uso en las bases de datos? ¿Son idénticas en CADA CASO? De lo contrario, puede Olvídese de ORDER BY en columnas varchar trabajando de la misma manera
REGEXP no distingue entre mayúsculas y minúsculas (a menos que se use con BINARY), y se puede usar, como ese ...
SELECT id FROM person WHERE name REGEXP ''john'';
... para que coincida con ''John'', ''JOHN'', ''john'', etc.
También puede considerar consultar el complemento searchlogic , que hace el cambio LIKE/ILIKE por usted.
También puede usar ~ * en postgres si quiere hacer coincidir una subcadena dentro de un bloque. ~ coincide con la subcadena sensible a las mayúsculas y minúsculas, ~ * subcadena insensible a mayúsculas y minúsculas. Es una operación lenta, pero podría ser útil para las búsquedas.
Select * from table where column ~* ''UnEvEn TeXt'';
Select * from table where column ~ ''Uneven text'';
Ambos acertarían en "Some Uneven text here" ("Aquí hay un texto desigual"). Solo el primero tocaría "Some UNEVEN HEE TEXT here".
Use Arel:
Author.where(Author.arel_table[:name].matches("%foo%"))
matches usará el operador ILIKE para Postgres, y LIKE para todo lo demás.
usa COLLATE.
select * from foo where upper(bar) = upper(?);
Si configura el parámetro en mayúsculas en la persona que llama, puede evitar la segunda llamada a la función.