Search code examples
mysqlsqldatabasesinglestore

Equivalent MemSQL query for MySQL query

Anybody could please help me in converting below MySQL query to MemSQL query.

SELECT TABLE_SCHEMA AS `schema`, TABLE_NAME AS `name`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME COLLATE utf8_general_ci IN (N'Record')
  AND TABLE_SCHEMA = 'test'

Need to solve the below error

[Error code:1064 SQL state:42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE utf8_general_ci IN (N'Record')AND TABLE_SCHEMA = 'test'' at line 1

Solution

  • You can use

    SELECT TABLE_SCHEMA AS `schema`, TABLE_NAME AS `name`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('Record')
  AND TABLE_SCHEMA = 'test';

    which is case-sensitive, or

    SELECT TABLE_SCHEMA AS `schema`, TABLE_NAME AS `name`
FROM INFORMATION_SCHEMA.TABLES
WHERE lower(TABLE_NAME) IN ('record')
  AND TABLE_SCHEMA = 'test';

    which is case-insensitive.

    The COLLATE clause isn't supported there, and N'string' isn't supported.