MySQL WHERE upiti sa uslovima, fokus na % džoker, razlika između \# i $ u MyBatis-u, kako MySQL obrađuje uzastopne % džokere
U prethodnom članku smo naučili jednostavne SELECT upite, ali bez upitnog uslova. Treba znati da u stvarnim poslovnim scenarijima količina podataka može biti vrlo velika, nekad i reda miliona ili desetina miliona slogova; bez upitnog uslova, izvlačenje svih podataka odjednom nije realno.
Zato se pri izvršavanju SELECT upita obično traži da se navede upitni uslov. U ovom odeljku ćemo naučiti neke jednostavne WHERE uslove.
I dalje ćemo kao primer koristiti tabelu članaka article iz projekta paicoding; na primer, ako želimo da pronađemo članak naslova „Razgovor o sharding-u", možemo napisati:
SELECT * FROM article WHERE title = 'Razgovor o sharding-u';
Ovde je WHERE title = 'Razgovor o sharding-u' upitni uslov, title je ime polja, a 'Razgovor o sharding-u' je vrednost polja.
Operatori upita poređenja
Pored gore pomenutog operatora =, MySQL pruža i niz drugih operatora upita poređenja; najčešće korišćeni su:
| Operator | Primer | Opis |
|---|---|---|
= | user_id = 1 | jednako |
!= ili <> | user_id != 1 ili user_id <> 1 | nije jednako |
> | user_id > 1 | veće |
< | user_id < 1 | manje |
>= | user_id >= 1 | veće ili jednako |
<= | user_id <= 1 | manje ili jednako |
Na primer, ako želimo da pronađemo članke sa user_id većim od 1000, možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id > 1000;
Ostale nećemo pojedinačno prikazivati; vrlo su jednostavni, možete ih i sami isprobati (nema ni potrebe 😂, dovoljno je da znate da takvi operatori poređenja postoje; kad zatreba, znaćete ih upotrebiti).
Upit po opsegu
Ako želimo da pronađemo članke sa user_id između 1000 i 2000, možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id >= 1000 AND user_id <= 2000;
Korišćenjem jednog >= i jednog <=, uz ključnu reč AND, može se realizovati upit po opsegu. Pored toga, MySQL pruža i ključnu reč BETWEEN, kojom se upit po opsegu realizuje konciznije (polje user_id ne mora da se navodi dvaput), na primer:
SELECT title, user_id, create_time FROM article WHERE user_id BETWEEN 1000 AND 2000;
Uz ključnu reč NOT može se izvesti i negacija upita po opsegu; na primer, ako želimo da pronađemo članke sa user_id van opsega 1000 do 2000, možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id NOT BETWEEN 1000 AND 2000;Upit po enumeraciji
Ako želimo da pronađemo članke čiji je user_id jednak 1, 2 ili 3, možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id IN (1, 2, 3);Dovoljno je da vrednost odgovara bilo kojoj iz enumeracije i slog će biti vraćen upitom.
I ovde, uz ključnu reč NOT, može se izvesti negacija enumerativnog upita; na primer, za članke čiji user_id nije 1, 2 ni 3, možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id NOT IN (1, 2, 3);NULL upit
U članku o svojstvima polja smo ranije spomenuli da je NULL posebna vrednost koja označava nepoznato ili nepostojeće.
Ne možemo direktno pomoću = ili != tražiti NULL vrednosti, već moramo koristiti IS NULL ili IS NOT NULL.
Ovde ćemo kratko objasniti zašto (može se pojaviti na intervjuu).
U SQL-u NULL predstavlja nepoznatu ili nedostajuću vrednost; on nije jednak praznom stringu, nuli niti bilo kojoj drugoj vrednosti.
Pošto je NULL nepoznat, svako poređenje sa drugom vrednošću (uključujući i drugi NULL) daje nepoznat rezultat. Logički, ne možemo reći da nepoznata vrednost jeste ili nije jednaka drugoj nepoznatoj vrednosti ili bilo kojoj konkretnoj vrednosti.
Prema SQL standardu, rezultat svake operacije poređenja sa NULL jeste NULL, što logički označava „nepoznato". To znači da izrazi column = NULL ili column != NULL ne daju TRUE ili FALSE, već NULL.
Da bi se ovaj problem rešio, SQL uvodi IS NULL i IS NOT NULL, namenjene isključivo proveri da li je kolona NULL. Ti operatori daju bulovu vrednost (TRUE ili FALSE) i mogu se direktno koristiti u logičkim izrazima.
Na primer, da bismo pronašli članke čiji je kratki naslov short_title NULL:
SELECT title, short_title, create_time FROM article WHERE short_title IS NULL;Za članke gde short_title nije NULL, možemo napisati:
SELECT title, short_title, create_time FROM article WHERE short_title IS NOT NULL;Naravno, da bi se upiti pojednostavili, prilikom projektovanja tabele projekat paicoding nastoji da izbegne upotrebu NULL, te se umesto njega koristi prazan string ili 0.
Tako, kada želimo da pronađemo članke čiji short_title nije prazan, možemo napisati:
SELECT title, short_title, create_time FROM article WHERE short_title != '';
Inače bismo morali na nivou SQL-a da napravimo jedan sloj konverzije za preduslove pretrage sa fronta, što je naporno.
Logički operatori
Pored gore pomenutih AND i NOT, MySQL pruža i OR, () i druge logičke operatore kojima se menja logika upitnog uslova; kombinovani, oni mogu služiti za sastavljanje više uslova.
Operator AND
Operator AND služi za kombinovanje više uslova; rezultat se vraća samo kada su svi uslovi zadovoljeni.
Na primer, ako želimo da pronađemo članke čiji user_id nije 1 i čiji je naslov „Razgovor o sharding-u", možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id != 1 AND title = 'Razgovor o sharding-u';
Operator OR
Operator OR takođe služi za kombinovanje više uslova, ali dovoljno je da samo jedan uslov bude zadovoljen da bi rezultat bio vraćen.
AND i OR su inače veoma česti i u programiranju; na primer && i || u Javi.
Na primer, ako želimo da pronađemo članke čiji je user_id jednak 1 ili čiji je naslov „Razgovor o sharding-u", možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id = 1 OR title = 'Razgovor o sharding-u';
Operator prioriteta
Operator malih zagrada () služi za promenu prioriteta uslova; na primer, ako želimo da pronađemo članke čiji user_id nije 1 i čiji je naslov „Razgovor o sharding-u" ili čiji short_title nije prazan, možemo napisati:
SELECT title, user_id, create_time FROM article WHERE user_id != 1 AND (title = 'Razgovor o sharding-u' OR short_title != '');Uporedite rezultate upita sa i bez malih zagrada — potpuno su različiti, jer je logički redosled drugačiji; ovo svako može da razume:
Upit sa džokerima
Ponekad ne treba nam precizna pretraga — dovoljna je neprecizna; tada nam u poslu pomažu džokeri (wildcards) 😁.
Na primer, ako želimo da pronađemo članke čiji naslov sadrži reč „distribuirano", možemo napisati:
SELECT title, user_id, create_time FROM article WHERE title LIKE '%distribuirano%';Ključna reč LIKE služi za neprecizne upite, dok gore korišćeni = spada u precizne upite.
MySQL podržava dva džokera, % i _, pri čemu % matchuje string proizvoljne dužine, dok _ matchuje tačno jedan znak.
Hajde da to detaljnije pogledamo.
Džoker %
Džoker % matchuje string proizvoljne dužine, uključujući i dužinu nula; vrlo je koristan pri pretrazi polja poput naslova članaka, korisničkih imena i sličnog.
Izvršimo prethodno pomenuti neprecizni upit za naslove koji sadrže „distribuirano":
Džoker % može se pojaviti na bilo kojoj poziciji u obrascu; na primer, ako želimo da pronađemo članke čiji naslov počinje sa „distribuirano", možemo napisati:
SELECT title, user_id, create_time FROM article WHERE title LIKE 'distribuirano%';Može se pojaviti i na početku obrasca, čime se označava završetak datim ključnim slogom:
SELECT title, user_id, create_time FROM article WHERE title LIKE '%distribuirano';Ipak, najčešće se pojavljuje na oba kraja obrasca. U projektu paicoding neprecizni upit realizujemo preko metode like koju pruža MyBatis-Plus; ona automatski dodaje % na oba kraja ključne reči.
Izvorni kod se nalazi u metodi concatLike klase SqlUtils, koja na osnovu pozicije džokera nadovezuje džoker %.
Naravno, neke složene SQL-ove ni MyBatis-Plus ne može uvek da zadovolji; tada SQL moramo napisati sami.
Definicija i sklapanje SQL iskaza obično se radi u Mapper.xml fajlu; na primer, administratorska strana projekta paicoding pri pretrazi članaka nepreciznu pretragu realizuje kroz vlastiti SQL.
Obratite pažnju na ovaj red SQL-a:
<if test="searchParams.title != null and searchParams.title != ''">
and a.title like concat('%', #{searchParams.title}, '%')
</if>Ako searchParams.title nije prazan, nadovezuje se džoker % i realizuje se neprecizna pretraga.
Metoda concat jeste MySQL-ova funkcija za nadovezivanje stringova.
# je u MyBatis-u oznaka mesta za parametar, koja se koristi u pripremljenim iskazima (PreparedStatement) radi sprečavanja SQL injection napada. Kada se parametar obavije sa #{}, MyBatis će pre izvršavanja SQL-a bezbedno popuniti vrednost tog parametra u SQL iskaz.
Obratite pažnju, ovo je jedno ispitno pitanje. Na intervjuu se može tražiti da se objasni razlika između # i $.
$ je takođe u MyBatis-u oznaka mesta za parametar, ali se koristi za direktno nadovezivanje SQL iskaza, bez pripreme. Kada se parametar obavije sa ${}, MyBatis direktno nadovezuje vrednost tog parametra na SQL iskaz.
Razlika između #{} i ${} je u tome što #{} koristi pripremljene iskaze i vrši bezbednosnu obradu parametara, dok ${} direktno nadovezuje i ne vrši nikakvu obradu parametara.
# i $ u MyBatis-u
Ovde ćemo se malo proširiti. Neka u mapper.xml fajlu SQL iskaz glasi ovako:
select * from user where name = #{name};Tada će on biti raščlanjen u:
select * from user where name = ?;Jedan #{} raščlanjuje se kao jedno mesto za parametar ?.
A ako u xml-u SQL iskaz glasi ovako:
select * from user where name = '${name}';tada se vrednost parametra direktno nadovezuje u iskaz, bez prethodne pripreme, pa ovakav oblik treba izbegavati kad god je parametar korisnički unos, jer otvara mogućnost SQL injection napada. Razlika između #{} (pripremljeni iskaz, bezbedna obrada) i ${} (direktno nadovezivanje, bez obrade) je upravo to što prvi štiti od injekcije, a drugi ne.