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%'
MySQL problem
Moderator/ica: Moderatori/ce
Pravila foruma
U naslovu teme unutar uglatih zagrada navesti o kojem jeziku je riječ. Primjer: [Java]
U naslovu teme unutar uglatih zagrada navesti o kojem jeziku je riječ. Primjer: [Java]
-
damir
- Postovi: 29
- Pridružen/a: 05 lip 2020, 08:43
- Spol: M
- OS: arch/ubuntu/xfce
- Lokacija: Srijem (Hr)
MySQL problem
If you cannot trust yourself, you cannot even trust your mistrust of yourself – so that without this underlying trust in the whole system of nature you are simply paralyzed.
Re: MySQL problem
Kod: Označi sve
> 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: Označi sve
> 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: Označi sve
> CREATE TABLE channels (
-> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(66) NOT NULL,
-> surname VARCHAR(66) NOT NULL
-> )
-> ;
Kod: Označi sve
> 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=latin1Es gibt keinen Gott, kein Universum, keine menschliche Rasse, kein irdisches Leben, keinen Himmel, keine Hölle. Es ist alles ein Traum - ein grotesker und dummer Traum. Nichts existiert außer dir. Und du bist nur ein Gedanke - ein vagabundierender Gedanke, ein nutzloser Gedanke, ein heimatloser Gedanke, der verloren in der leeren Ewigkeit wandelt!
-
damir
- Postovi: 29
- Pridružen/a: 05 lip 2020, 08:43
- Spol: M
- OS: arch/ubuntu/xfce
- Lokacija: Srijem (Hr)
Re: MySQL problem
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!
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!
If you cannot trust yourself, you cannot even trust your mistrust of yourself – so that without this underlying trust in the whole system of nature you are simply paralyzed.
Re: MySQL problem
Kod: Označi sve
> 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)
Es gibt keinen Gott, kein Universum, keine menschliche Rasse, kein irdisches Leben, keinen Himmel, keine Hölle. Es ist alles ein Traum - ein grotesker und dummer Traum. Nichts existiert außer dir. Und du bist nur ein Gedanke - ein vagabundierender Gedanke, ein nutzloser Gedanke, ein heimatloser Gedanke, der verloren in der leeren Ewigkeit wandelt!
Re: MySQL problem
Kod: Označi sve
> 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)
Es gibt keinen Gott, kein Universum, keine menschliche Rasse, kein irdisches Leben, keinen Himmel, keine Hölle. Es ist alles ein Traum - ein grotesker und dummer Traum. Nichts existiert außer dir. Und du bist nur ein Gedanke - ein vagabundierender Gedanke, ein nutzloser Gedanke, ein heimatloser Gedanke, der verloren in der leeren Ewigkeit wandelt!
Re: MySQL problem
Ukratko čak i kada nije latin1 već utf8, radi case insensitive. Meni se čini da ti imaš neki čudni charset.
Es gibt keinen Gott, kein Universum, keine menschliche Rasse, kein irdisches Leben, keinen Himmel, keine Hölle. Es ist alles ein Traum - ein grotesker und dummer Traum. Nichts existiert außer dir. Und du bist nur ein Gedanke - ein vagabundierender Gedanke, ein nutzloser Gedanke, ein heimatloser Gedanke, der verloren in der leeren Ewigkeit wandelt!
Re: MySQL problem
latin1:
utf8mb4:
edit: i na mariadb radi isto kao i na perconi.
Kod: Označi sve
mysql> select vmssid from videos where vmssid like '%A5BC5%';
+----------------------------------------------------------------------------------------------------------------------------------+
| vmssid |
+----------------------------------------------------------------------------------------------------------------------------------+
| 17715143a5bc5b59088b63ce634blablablaovajdioidjajeizbacena15cb03a6 |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Kod: Označi sve
mysql> select user_login from wp_users where user_login like '%MBM%';
+------------+
| user_login |
+------------+
| mbm |
+------------+
1 row in set (0.00 sec)
Kod: Označi sve
# 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.
Re: MySQL problem
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)damir je napisao/la: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!
probaj
Kod: Označi sve
ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4 Kod: Označi sve
SELECT * FROM channels WHERE name like '%Abc%'-
damir
- Postovi: 29
- Pridružen/a: 05 lip 2020, 08:43
- Spol: M
- OS: arch/ubuntu/xfce
- Lokacija: Srijem (Hr)
Re: MySQL problem
iweb je napisao/la: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)damir je napisao/la: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!
probaji onda pokrenutiKod: Označi sve
ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4Kod: Označi sve
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...
If you cannot trust yourself, you cannot even trust your mistrust of yourself – so that without this underlying trust in the whole system of nature you are simply paralyzed.
Re: MySQL problem
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
Es gibt keinen Gott, kein Universum, keine menschliche Rasse, kein irdisches Leben, keinen Himmel, keine Hölle. Es ist alles ein Traum - ein grotesker und dummer Traum. Nichts existiert außer dir. Und du bist nur ein Gedanke - ein vagabundierender Gedanke, ein nutzloser Gedanke, ein heimatloser Gedanke, der verloren in der leeren Ewigkeit wandelt!