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.
:hmm

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