MariaDB / regexp_replace()

 ( log69 | 2018. március 13., kedd - 18:35 )

Két napja váltottam MySQL-ről MariaDB-re, lásd bejegyzés itt. Az oka a következő volt:

Fejlesztett megoldásom adatkezelő felületén eddig is volt lehetőség tömeges adatcserére. Jól jön ha például egy táblában egy oszlop bizonyos adatából az összeset át kell írnunk. Eddig úgy működött, hogy mindig a teljes cella értékét cseréltem. De mi van akkor, ha csak részleges cserét akarunk végezni? Például azért, mert minden Horváth nevű embert h betű nélkül vittek fel, és mindenhol javítani kell, viszont különböző keresztnevek vannak és így a cellák értéke nem azonos?

Ezt MariaDB regexp_replace() nevű függvényével végzem. Mivel a függvény illesztést is végez, így nem kell külön "where" paraméter. Bejött viszont még egy probléma:

Alapból mindenhol a legnagyobb kényelmet és egyszerűséget preferálom ha keresésről van szó. Nem számít a kis- és nagybetű, sem pedig az ékezetek. Tehát "jeno" kulcsszóra keresve hozza a "JENŐ"-t is és fordítva, szerepeljen bárhol a szövegben. Illetve több szótöredék is megadható szóközzel elválasztva, ekkor mindegyiknek szerepelnie kell a cellában. Sorrendjük nem számít. Továbbá glob pattern-t is engedek használni: "*" bármennyi és bármilyen karaktert jelent, "?" pedig egyet.

Nem szabad túlbonyolítani, különben a felhasználónak nem lesz hatékony a megoldás és nem fog tudni emlékezni, vagy pedig hiába emlékezik, a fejben cipelendő "működési kézikönyv" lesz nagy terjedelmű. Hiába tud egy szoftver sokat, ha nem tudjuk a kényelmi zónán belülre hozni a mindennapokban.


Keresésnél tehát a kulcsszavakból konvertálok mindig MySQL pattern-t és az SQL hívásnál a "like" paranccsal szűrök rá, például (kulcsszó = "horv janos"):

select * from mytable where mycolumn like '%horv%janos%';

Teljes csere egyszerű, csupán lecserélem a kiválasztott sorok adott oszlopának értékét.

update mytable set mycolumn = 'mydata' where mycolumn like '%horv%janos%';

Részleges csere viszont így néz ki:

Mivel a regexp_replace() kis-nagybetű érzéketlen ha az adatbázis is erre van beállítva, így a kis-nagybetű érzéketlenség megoldva alapból. Viszont az ékezet nélküli szűrés nem olyan egyszerű. Ezt úgy oldottam meg, hogy a keresési kulcsszavakból az alábbi módon konvertálok regexp-et:

Van két ilyen string-em (több sorba tettem az olvashatóságért):

# accented chars matching unaccented ones
C_accent_on  = "ÀÁÂÃÄÅàáâãäåĀāĂ㥹ÇçĆćĈĉĊċČčÐðĎďĐđÈÉÊËèéêëĒēĔĕĖėĘęĚě
ĜĝĞğĠġĢģĤĥĦħÌÍÎÏìíîïĨĩĪīĬĭĮįİıĴĵĶķĸĹĺĻļĽľĿŀŁłÑñŃńŅņŇňʼnŊŋÒÓÔÕÖØòóôõöø
ŌōŎŏŐőŔŕŖŗŘřŚśŜŝŞşŠšſŢţŤťŦŧÙÚÛÜùúûüŨũŪūŬŭŮůŰűŲųŴŵÝýÿŶŷŸŹźŻżŽž"

C_accent_off = "AAAAAAaaaaaaAaAaAaCcCcCcCcCcDdDdDdEEEEeeeeEeEeEeEeEe
GgGgGgGgHhHhIIIIiiiiIiIiIiIiIiJjKkkLlLlLlLlLlNnNnNnNnnNnOOOOOOoooooo
OoOoOoRrRrRrSsSsSsSssTtTtTtUUUUuuuuUuUuUuUuUuUuWwYyyYyYZzZzZz"

A konvertálás során pedig minden egyes karaktert lecserélek az alsóból a felső összes találatára. Tehát a "on*e" keresési szavam így fog kinézni konverzió után:

pattern = "[oOÒÓÔÕÖØòóôõöøŌōŎŏŐő][nNÑñŃńŅņŇňʼnŊŋ].*?[eEÈÉÊËèéêëĒēĔĕĖėĘęĚě]"

Habár itt most a kis- és nagybetű nem számít és ezért az első két karakter helyett állhatna egy is az első kis és második nagy helyett, de általánosabbra írva meg a kódot a fenti az eredmény. Még meglátom hogy hogyan fogok optimalizálni. A fentit az alábbi kód eredményezi (Ruby):

def text_noaccent_downcase(text)
  return text.force_encoding("UTF-8"). \
    tr( C_accent_on, C_accent_off ).downcase
end

def create_regexp(text, flag_no_accent = nil)
  u_accent = (flag_no_accent ? C_accent_off.downcase : "")
  u_accent_rev = u_accent.reverse
  u_accent_len = u_accent.size

  regex = ""
  text.chars.each_with_index {|c, i|
    # use the char "*" as glob pattern
    if c == "*"
      # last char? then use greedy match
      if i == (text.size - 1)
        regex << ".*"
      else
        regex << ".*?"
      end
    elsif c == "?"
      regex << "."
    elsif c.match(/[0-9]/)
      regex << "#{c}"
    elsif c.match(/[a-zA-Z]/) or c.bytes.size >= 2

      if flag_no_accent
        c2 = text_noaccent_downcase(c)
        i1 = u_accent.index(c2)
        if i1
          i2 = u_accent_len - 1 - u_accent_rev.index(c2)
          c3 = "#{c}#{c2}#{c2.upcase}#{C_accent_on[i1..i2]}". \
            chars.uniq.join
          regex << "[#{c3}]"
        else
          c3 = "#{c}#{c2}#{c2.upcase}"
          regex << "[#{c3}]"
        end

      else
        regex << "#{c}"
      end
    else
      regex << "\\#{c}"
    end
  }
  return regex
end


create_regexp("on*e", 1)

Van még egy csavar. Ha több kulcsszót adunk meg szóközzel elválasztva, akkor külön lekonvertálom mindet és egy "|" karakterrel logikai VAGY kapcsolatot csinálok a regexp-hez.

És végül a replace hívás részleges cseréhez (pattern értéke feljebb látható):

update mytable set mycolumn = regexp_replace(mycolumn, pattern, mydata);

Egyéb érdekességek:

  1. numerikus oszlopnál kicserélhetjük önmagára az értéket, matematikai műveletet végezve rajta, ahol x hivatkozik az eredeti értékre, például "*"-ra szűrűnk (összes kijelöl), majd átírjuk erre: x * 1.27, ezzel felszorozzuk áfásra
  2. ahogy a features részben látszik, lehet használni matematikai kifejezéseket is, például pi() * sqrt(2) + sin(x)
  3. egyszerre több oszlop is szűrhető, ekkor ÉS kapcsolat van a szűrési kulcsszavak között, például adott megrendelőhöz keressük hogy mikor vett egy adott árut és milyen mennyiségben

Egy rövid videó a működésről:

video

Hozzászólás megjelenítési lehetőségek

A választott hozzászólás megjelenítési mód a „Beállítás” gombbal rögzíthető.

Keresésnél miért like? Szerintem nem hatékony. A match against-al használja az indexet csak fulltext searchnak kell lennie a táblának.
Több szónál szétszeded tömbbe és mindegyik szó elé illesztesz egy + jelet.
Lehet több oszlopba is keresni egyszerre.
Szerintem gyorsabb mint a like.

pch
--
SB-soft online ügyviteli rendszer
--

Általános adatkezelő, folyamatosan változnak a táblák, nem én határozom meg a struktúrát hanem a felhasználók. Ezért nem készítek indexet, mert nem ismert hogy mire lenne érdemes elkészíteni.

A WHERE oldal eléggé bonyolult, nem ilyen egyszerű mint a fenti példámban. Sok egymásba ágyazott feltétel van, dátum konverziókkal stb. Ezért a LIKE.

Illetve fulltext index csak MyISAM táblára tehető ahogy egyébként olvasom is. InnoDB-t használok.

Kösz.

Innodb-re is van én is azt használom.
Bár így hogy írod megértem a LIKE-t.

pch
--
SB-soft online ügyviteli rendszer
--