Stranica: 1/3.
MySQL problem
Postano: 09 lip 2020, 21:05
Postao/la damir
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%'
Re: MySQL problem
Postano: 09 lip 2020, 22:43
Postao/la shrike
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)
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: 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=latin1
Re: MySQL problem
Postano: 10 lip 2020, 09:04
Postao/la damir
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!
Re: MySQL problem
Postano: 10 lip 2020, 10:49
Postao/la shrike
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)
Re: MySQL problem
Postano: 10 lip 2020, 11:16
Postao/la shrike
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)
Re: MySQL problem
Postano: 10 lip 2020, 11:18
Postao/la shrike
Ukratko čak i kada nije latin1 već utf8, radi case insensitive. Meni se čini da ti imaš neki čudni charset.
Re: MySQL problem
Postano: 10 lip 2020, 16:28
Postao/la iweb
latin1:
Kod: Označi sve
mysql> select vmssid from videos where vmssid like '%A5BC5%';
+----------------------------------------------------------------------------------------------------------------------------------+
| vmssid |
+----------------------------------------------------------------------------------------------------------------------------------+
| 17715143a5bc5b59088b63ce634blablablaovajdioidjajeizbacena15cb03a6 |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
utf8mb4:
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.
edit: i na mariadb radi isto kao i na perconi.

Re: MySQL problem
Postano: 10 lip 2020, 16:32
Postao/la iweb
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!
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: Označi sve
ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4
i onda pokrenuti
Kod: Označi sve
SELECT * FROM channels WHERE name like '%Abc%'
Re: MySQL problem
Postano: 10 lip 2020, 16:59
Postao/la damir
iweb je napisao/la: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!
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: Označi sve
ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4
i onda pokrenuti
Kod: 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...
Re: MySQL problem
Postano: 10 lip 2020, 19:38
Postao/la shrike
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