MySQL problem

Znaći, u stupcu imam red sa 'abc' i red sa 'Abc'. Kako da napravim
case-insensitive query?

Ovo sa BINARY ne štima:

SELECT `id` FROM `channels` WHERE BINARY `name` LIKE '%Abc%'

Kod:

> select * from channels;
+----+---------+---------+
| id | name    | surname |
+----+---------+---------+
|  1 | abc     | sotona  |
|  2 | abc     | sotona  |
|  3 | Abc     | sotona  |
|  4 | ABc     | sotona  |
|  5 | aBc     | sotona  |
|  6 | aBC     | sotona  |
|  7 | Lucifer | sotona  |
+----+---------+---------+

Kod:

> select * from channels where name like "%abc%";
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | abc  | sotona  |
|  2 | abc  | sotona  |
|  3 | Abc  | sotona  |
|  4 | ABc  | sotona  |
|  5 | aBc  | sotona  |
|  6 | aBC  | sotona  |
+----+------+---------+
6 rows in set (0.001 sec)

binary ne štima I ima smisla da ne radi jer nisu binarno isti. Kao što vidiš u gorenjem primjeru, obični select radi. Tablica ovako izgleda (tj ovako sam ju kreirao):

Kod:

> CREATE TABLE channels (
    -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(66) NOT NULL,
    -> surname VARCHAR(66) NOT NULL
    -> )
    -> ;


Kod:

> show create table channels;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                            |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| channels | CREATE TABLE `channels` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(66) NOT NULL,
  `surname` varchar(66) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

Sry, trebao sam bolje postaviti pitanje.
Imam table oko 50MB i default charset je utf8. Pa sam probao:

ALTER TABLE `channels` COLLATE utf8_bin;

Al ni to nije radilo. Kasnije sam ovo iskopao na mariadb blogu:

SELECT * FROM `channels` WHERE CONVERT(`name` USING utf8mb4) LIKE '%Abc%'

I to radi!

Kod:

> alter table channels CHARSET=utf8;
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [kuro]> show create table channels;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                    |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| channels | CREATE TABLE `channels` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(66) CHARACTER SET latin1 NOT NULL,
  `surname` varchar(66) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [kuro]> select * from channels where name like "%abc%";
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | abc  | sotona  |
|  2 | abc  | sotona  |
|  3 | Abc  | sotona  |
|  4 | ABc  | sotona  |
|  5 | aBc  | sotona  |
|  6 | aBC  | sotona  |
+----+------+---------+
6 rows in set (0.001 sec)


Kod:

> show create table channels;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                          |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| channels | CREATE TABLE `channels` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(66) NOT NULL,
  `surname` varchar(66) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [kuro]> select * from channels where name like "%abc%"; ; 
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  2 | abc  | sotona  |
|  3 | Abc  | sotona  |
|  4 | AbC  | sotona  |
|  5 | ABC  | sotona  |
|  6 | aBC  | sotona  |
|  7 | aBc  | sotona  |
+----+------+---------+
6 rows in set (0.000 sec)


Ukratko čak i kada nije latin1 već utf8, radi case insensitive. Meni se čini da ti imaš neki čudni charset.

latin1:

Kod:

mysql> select vmssid from videos where vmssid like '%A5BC5%';
+----------------------------------------------------------------------------------------------------------------------------------+
| vmssid                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------+
| 17715143a5bc5b59088b63ce634blablablaovajdioidjajeizbacena15cb03a6 |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


utf8mb4:

Kod:

mysql> select user_login from wp_users where user_login like '%MBM%';
+------------+
| user_login |
+------------+
| mbm        |
+------------+
1 row in set (0.00 sec)


Kod:

# mysql --help
mysql  Ver 14.14 Distrib 5.7.29-32, for debian-linux-gnu (x86_64) using  7.0
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.


edit: i na mariadb radi isto kao i na perconi.

Sry, trebao sam bolje postaviti pitanje.
Imam table oko 50MB i default charset je utf8. Pa sam probao:

ALTER TABLE `channels` COLLATE utf8_bin;

Al ni to nije radilo. Kasnije sam ovo iskopao na mariadb blogu:

SELECT * FROM `channels` WHERE CONVERT(`name` USING utf8mb4) LIKE '%Abc%'

I to radi!

e pa e, tu ti može biti problem ako nisi dobro konvertirao, a da je prije bilo pohranjeno u nekom charsetu koji ti to nije podržavao (tipa _bin charseta, eventualno - ali nisam siguran kakvo je stanje s _bin charsetima, da budem iskren)

probaj

Kod:

ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4

i onda pokrenuti

Kod:

SELECT * FROM channels WHERE name like '%Abc%'

Sry, trebao sam bolje postaviti pitanje.
Imam table oko 50MB i default charset je utf8. Pa sam probao:

ALTER TABLE `channels` COLLATE utf8_bin;

Al ni to nije radilo. Kasnije sam ovo iskopao na mariadb blogu:

SELECT * FROM `channels` WHERE CONVERT(`name` USING utf8mb4) LIKE '%Abc%'

I to radi!

e pa e, tu ti može biti problem ako nisi dobro konvertirao, a da je prije bilo pohranjeno u nekom charsetu koji ti to nije podržavao (tipa _bin charseta, eventualno - ali nisam siguran kakvo je stanje s _bin charsetima, da budem iskren)

probaj

Kod:

ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4

i onda pokrenuti

Kod:

SELECT * FROM channels WHERE name like '%Abc%'

Hvala. Da trebo sam utf8mb4 a ne utf8_bin.

ALTER DATABASE project_x CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

U biti imam csv fajl od par GB-a. Pa sam napisao js (node) skriptu koja ga
konvertira u sql. U pola svega sam imao problema sa case-insensitive
string (jer channels table je one-to-many sql relacija). Pa sam našao
negdje na stackoverflowu da postavim char set u utf8_bin...

CSV fajl se može direktno importati u mysql, uopće ti ne treba neki program. https://dev.mysql.com/doc/refman/8.0/en/load-data.html