MySQL Toiminnot: merkkijono, numeerinen, käyttäjän määrittämä, tallennettu
⚡ Älykäs yhteenveto
MySQL Funktiot muuntavat dataa ennen sen tallentamista tai hakemista ja palauttavat yhden lasketun tuloksen. Tässä artikkelissa selitetään sisäänrakennetut merkkijono-, numeeriset ja päivämääräfunktiot ja näytetään, kuinka tallennetut ja käyttäjän määrittämät funktiot laajentavat itse tietokantamoottoria.

Mitä ovat MySQL Toiminnot?
MySQL voi tehdä paljon muutakin kuin vain tallentaa ja hakea tietoja. Se voi myös suorittaa tietojen käsittelyä ennen sen hakemista tai tallentamista. Siinä kohtaa MySQL Funktiot tulevat mukaan kuvaan. Funktiot ovat yksinkertaisesti koodinpätkiä, jotka suorittavat toiminnon ja palauttavat sitten tuloksen. Jotkut funktiot hyväksyvät parametreja, kun taas toiset funktiot eivät hyväksy parametreja.
Katsotaanpa lyhyesti esimerkkiä. Oletusarvoisesti MySQL tallentaa päivämäärätietotyypit muodossa ”VVVV-KK-PP”. Oletetaan, että olemme rakentaneet sovelluksen ja käyttäjämme haluavat päivämäärän palautettavan muodossa ”PP-KK-VVVV”. Voimme käyttää MySQL sisäänrakennettu DATE_FORMAT-funktio tämän saavuttamiseksi. DATE_FORMAT on yksi käytetyimmistä funktioista MySQL, ja tarkastelemme sitä tarkemmin myöhemmin tässä oppitunnissa.
Olipa funktion tyyppi mikä tahansa, palauttaa aina yhden arvon, voi hyväksyä nolla tai useampia parametreja sulkujen sisällä ja voidaan käyttää missä tahansa, missä lauseke on sallittu — SELECT-luettelossa, WHERE-lauseessa tai ORDER BY -lauseessa.
Miksi käyttää MySQL Toiminnot?
Nyt kun tiedämme, mikä funktio on, seuraava kysymys on, miksi meidän pitäisi ylipäätään syöttää tätä työtä tietokantaan.
Kuten yllä oleva kaavio osoittaa, funktio ottaa syötteenä arvon, soveltaa logiikkaa tietokantamoottorin sisällä ja palauttaa yhden tuloksen jokaiselle sitä pyytävälle sovellukselle.
Ohjelmoijat saattavat ajatella: "Miksi vaivautua MySQL Funktiot? Sama vaikutus voidaan saavuttaa skriptaus- tai ohjelmointikielellä." On totta, että voimme saavuttaa sen kirjoittamalla proseduurin sovellusohjelmaan.
Palataanpa DATE-esimerkkiimme. Jotta käyttäjämme saisivat tiedot halutussa muodossa, liiketoimintatason on itse suoritettava tarvittava käsittely.
Tästä tulee ongelma, kun sovellus on integroitava muihin järjestelmiin. Kun käytämme MySQL funktiot, kuten DATE_FORMAT, on upotettu tietokantaan, ja mikä tahansa tietoja tarvitseva sovellus saa ne vaaditussa muodossa. Tämä vähentää liiketoimintalogiikan uudelleentyöstöä ja vähentää datan epäjohdonmukaisuuksia.
Toinen syy harkita MySQL toimintojen ominaisuus on, että ne voivat auttaa vähentämään verkkoliikennettä asiakas-palvelinsovelluksissaLiiketoimintakerroksen tarvitsee vain kutsua tallennettua funktiota ilman, että raakatietoja tarvitsee vetää verkon yli niiden manipuloimiseksi. Funktioiden käyttö voi keskimäärin parantaa huomattavasti järjestelmän kokonaissuorituskykyä.
Tyyppisiä MySQL Tehtävät
Kun "mitä" ja "miksi" on selvitetty, voimme nyt tarkastella kolmea funktioperhettä MySQL tarjoaa: sisäänrakennettuja funktioita, tallennettuja funktioita ja käyttäjän määrittämiä funktioita.
Sisäänrakennetut toiminnot
MySQL mukana tulee useita sisäänrakennettuja funktioita — funktioita, jotka on jo toteutettu MySQL palvelin. Niiden avulla voimme suorittaa monenlaisia tiedon käsittelytoimia, ja ne kuuluvat seuraaviin yleisesti käytettyihin ryhmiin.
- Merkkijonofunktiot – käyttää merkkijonotietotyyppejä
- Numeeriset funktiot – käyttää numeerisia tietotyyppejä
- Päivämäärätoiminnot – käyttää päivämäärätietotyyppejä
- Kokoomatoiminnot – käyttää kaikkia yllä olevia tietotyyppejä ja tuottaa yhteenvetotulosjoukkoja.
- Muita toimintoja - MySQL tukee myös muita sisäänrakennettuja funktioita, mutta rajoitamme tämän oppitunnin yllä mainittuihin ryhmiin.
Tarkastellaan nyt yksityiskohtaisesti kutakin yllä mainittua ryhmää. Selitämme käytetyimmät funktiot käyttämällä "Myflixdb"-esimerkkitietokantaamme.
Merkkijonofunktiot
Merkkijonofunktiot käsittelevät tekstiarvoja. Elokuvataulukossamme otsikot tallennetaan käyttämällä sekä pieniä että isoja kirjaimia. Oletetaan, että haluamme kyselyn, joka palauttaa otsikot isoilla kirjaimilla. ”UCASE”-funktio ottaa parametrina merkkijonon ja muuntaa jokaisen kirjaimen isoksi kirjaimeksi, kuten alla oleva skripti osoittaa.
SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;
TÄÄLTÄ
- UCASE(`otsikko`) on sisäänrakennettu funktio, joka ottaa parametrina otsikon ja palauttaa sen isoilla kirjaimilla kirjoitettuna.
- AS `iso_kirjain_otsikko` antaa lasketulle sarakkeelle aliaksen, joten tulosjoukossa on luettava otsikko raakalausekkeen sijaan.
Suoritetaan yllä oleva komentosarja MySQL Workbench Myflixdb:tä vasten antaa meille alla olevat tulokset.
| elokuvan_tunnus | otsikko | isoilla_kirjaimilla_kirjattu_otsikko |
|---|---|---|
| 16 | 67% syyllinen | 67 % SYYLLISIÄ |
| 6 | enkelit ja demonit | ENKELIT JA DEMONIT |
| 4 | Code Nimi Musta | KOODI NIMI MUSTA |
| 5 | Isän pienet tytöt | Isän pienet tytöt |
| 7 | DaVinci Code | DA VINCI-KOODI |
| 2 | Sarah Marshal unohdetaan | SARAH MARSHALIN UNOHTAMINEN |
| 9 | Honey mooners | HUNAJA MOONERS |
| 19 | elokuva 3 | ELOKUVA 3 |
| 1 | Pirates of the Caribbean 4 | Karibian merirosvot 4 |
| 18 | esimerkkielokuva | ESIMERKKIELOKUVA |
| 17 | Diktaattori | SUURI DIKTAATTORI |
| 3 | X-Men | X-MEN |
Kaksi kumppania kannattaa muistaa UCASE:n rinnalla: LCASE muuntaa merkkijonon pieniksi kirjaimiksi ja cONCAT yhdistää kaksi tai useampia merkkijonoja yhdeksi. Täydellinen luettelo löytyy MySQL merkkijonofunktion viite.
Numeeriset funktiot
Kuten aiemmin mainittiin, numeeriset funktiot käsittelevät numeerisia tietotyyppejä. Voimme myös suorittaa matemaattisia laskutoimituksia numeeriselle datalle suoraan SQL-lausekkeissamme.
Aritmeettiset operaattorit
MySQL tukee seuraavia aritmeettisia operaattoreita, joita voidaan käyttää laskutoimitusten suorittamiseen SQL-lausekkeissa.
| Nimi | Tuotetiedot |
|---|---|
| DIV | Kokonaislukujako |
| / | Divisioona |
| - | allatracTUKSEN |
| + | Lisäys |
| * | Kertolasku |
| % tai MOD | modulus |
Esimerkkejä kustakin operaattorista on seuraavassa.
Kokonaislukujen jakolasku (DIV) — DIV hylkää desimaaliosan ja palauttaa vain kokonaisluvun.
SELECT 23 DIV 6;
Yllä olevan skriptin suorittaminen antaa meille 3.
Jaostooperaattori (/) — toisin kuin DIV, jakolaskuoperaattori säilyttää tuloksen desimaaliosan.
SELECT 23 / 6;
Yllä olevan skriptin suorittaminen antaa meille 3.8333.
allatraction-operaattori (-)
SELECT 23 - 6;
Yllä olevan skriptin suorittaminen antaa meille 17.
Lisäysoperaattori (+)
SELECT 23 + 6;
Yllä olevan skriptin suorittaminen antaa meille 29.
Kertooperaattori (*)
SELECT 23 * 6 AS `multiplication_result`;
Tulos:
| kertolasku_tulos |
|---|
| 138 |
Modulo-operaattori (% tai MOD)
Modulo-operaattori jakaa N:n M:llä ja antaa meille jakojäännöksen. Tarkastellaan modulo-operaattoriesimerkkiä käyttäen samoja arvoja kuin edellisissä esimerkeissä.
SELECT 23 % 6; -- OR, equivalently: SELECT 23 MOD 6;
Kumman tahansa skriptin suorittaminen antaa meille 5.
Tarkastellaan nyt joitain yleisiä numeerisia funktioita MySQL.
LATTIA – tämä funktio poistaa desimaalit luvusta ja pyöristää sen alaspäin lähimpään kokonaislukuun. Alla oleva skripti havainnollistaa sen käyttöä.
SELECT FLOOR(23 / 6) AS `floor_result`;
Tulos:
| lattiatulos |
|---|
| 3 |
ROUND – tämä funktio pyöristää luvun lähimpään kokonaislukuun. Koska 23/6 on 3.8333, ROUND palauttaa arvon 4, kun taas FLOOR palauttaa arvon 3 – näitä kahta ei voi vaihtaa keskenään.
SELECT ROUND(23 / 6) AS `round_result`;
Tulos:
| kierroksen_tulos |
|---|
| 4 |
RAND – tämä funktio luo satunnaisluvun. Sen arvo muuttuu joka kerta, kun funktiota kutsutaan. Alla oleva skripti havainnollistaa sen käyttöä.
SELECT RAND() AS `random_result`;
Päivämäärätoiminnot
Päivämääräfunktiot käsittelevät päivämäärä- ja päivämäärä-kellonaika-tietotyyppejä. DATE_FORMAT on funktio, joka ratkaisee johdannossa kuvatun "VVVV-KK-PP vs. PP-KK-VVVV" -ongelman.
PÄIVÄMÄÄRÄMUOTO ottaa kaksi parametria: muotoiltavan päivämäärän arvon ja paikkamerkeistä muodostetun muotoilumerkkijonon. Alla oleva komentosarja palauttaa jokaisen julkaisupäivämäärän käyttäjien pyytämässä päivä-kuukausi-vuosi -muodossa.
SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date` FROM `movies`;
Yleisimmin käytetyt muotoilun paikkamerkit on lueteltu alla.
| Paikanpitäjä | Merkitys | Esimerkki tulosteesta |
|---|---|---|
| %d | Kuukauden päivä, kaksi numeroa | 04 |
| %m | Kuukausi, kaksi numeroa | 08 |
| %Y | Vuosi, neljä numeroa | 2012 |
| %M | Kuukauden nimi kokonaisuudessaan | elokuu |
| %Hänen | Hours, minuutteja, sekunteja | 14:35:09 |
Kolme muuta päivämääräfunktiota esiintyy jatkuvasti jokapäiväisessä työssä:
- CURDATE() palauttaa nykyisen päivämäärän muodossa VVVV-KK-PP.
- NYT() palauttaa nykyisen päivämäärän ja ajan.
- PÄIVÄYSEROTUS(d1, d2) palauttaa kahden päivämäärän välisten päivien määrän – minkä tahansa myöhästyneiden vuokrausten raportin perustan.
Täydellinen luettelo on osoitteessa MySQL päivämäärä- ja aikafunktion viite.
Tallennetut toiminnot
Sisäänrakennetut funktiot kattavat yleisimmät tapaukset. Kun liiketoimintasääntö on tarkempi, kirjoitamme oman – ja sitä varten on olemassa tallennettu funktio.
Tallennetut funktiot toimivat aivan kuten sisäänrakennetut funktiot, paitsi että määrittelet ne itse. Kun tallennettu funktio on luotu, sitä voidaan käyttää SQL-lauseissa täsmälleen kuten mitä tahansa muuta funktiota. Perussyntaksi on esitetty alla.
CREATE FUNCTION sf_name ([parameter(s)]) RETURNS data_type [DETERMINISTIC | NOT DETERMINISTIC] BEGIN -- procedural statements END
TÄÄLTÄ
- “CREATE FUNCTION sf_name ([parametri(t)])” on pakollinen ja kertoo MySQL palvelimelle luodakseen funktion nimeltä `sf_name`, jonka suluissa on määritelty valinnaiset parametrit.
- ”PALAUTAA tietotyyppi” on pakollinen ja määrittää funktion palauttaman tietotyypin.
- "DETERMINISTINEN" deklaroi, että funktio palauttaa saman arvon aina, kun annetaan samat argumentit. "EI DETERMINISTINEN" julistaa päinvastaista.
- "ALKU … LOPPU" käärii funktion suorittaman proseduraalisen koodin.
Oletetaan, että haluamme tietää, mitkä vuokraelokuvat ovat myöhässä. Voimme luoda tallennetun funktion, joka hyväksyy palautuspäivämäärän parametrina ja vertaa sitä palvelimella olevaan nykyiseen päivämäärään. Jos nykyinen päivämäärä on myöhässä kuin palautuspäivämäärä, elokuva on myöhässä ja palautamme arvon "Kyllä"; muuten palautamme arvon "Ei".
DELIMITER | CREATE FUNCTION sf_past_movie_return_date (return_date DATE) RETURNS VARCHAR(3) NOT DETERMINISTIC BEGIN DECLARE sf_value VARCHAR(3); IF CURDATE() > return_date THEN SET sf_value = 'Yes'; ELSEIF CURDATE() <= return_date THEN SET sf_value = 'No'; END IF; RETURN sf_value; END| DELIMITER ;
⚠️ Varoitus — älä nimeä tätä funktiota DETERMINISTISEKSI. Runko kutsuu CURDATE()-funktiota, joten sama argumentti voi palauttaa tänään arvon ”Ei” ja huomenna arvon ”Kyllä”. Aikariippuvaisen DETERMINISTIC-funktion määrittäminen johtaa optimoijaa harhaan eikä ole turvallinen lausepohjaiselle replikoinnille. Käytä EI DETERMINISTINEN aina kun runko kutsuu CURDATE()-, NOW()- tai RAND()-funktioita.
Yllä olevan komentosarjan suorittaminen luo tallennetun funktion `sf_past_movie_return_date`. Testataanpa sitä nyt.
SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(), sf_past_movie_return_date(`return_date`) AS `is_overdue` FROM `movierentals`;
Suoritetaan yllä oleva komentosarja MySQL Workbenchin vertailu myflixdb-tiedostoa vastaan antaa seuraavat tulokset.
| elokuvan_tunnus | jäsennumero | palautuspäivä | CURDATE() | on myöhässä |
|---|---|---|---|---|
| 1 | 1 | NULL | 04-08-2012 | NULL |
| 2 | 1 | 25-06-2012 | 04-08-2012 | Kyllä |
| 2 | 3 | 25-06-2012 | 04-08-2012 | Kyllä |
| 2 | 2 | 25-06-2012 | 04-08-2012 | Kyllä |
| 3 | 3 | NULL | 04-08-2012 | NULL |
Huomaa kaksi NULL-riviä. Kun `return_date` on NULL, molemmat vertailut antavat tulokseksi NULL eikä TRUE- tai FALSE-arvoa, joten kumpikaan JOS-haara ei toimi ja funktio palauttaa NULL-arvon – odotetun tuloksen, koska palauttamattomalla elokuvalla ei ole paluupäivämäärää, johon verrata.
Käyttäjän määrittämät toiminnot
Kun SQL yksinään ei riitä nopeudella, MySQL sallii kolmannen vaihtoehdon. Käyttäjän määrittämät funktiot (UDF) kirjoitetaan käännettävällä kielellä, kuten C or C++, jotka on rakennettu jaettuun kirjastoon ja rekisteröity palvelimelle. Lisäyksen jälkeen niitä kutsutaan aivan kuten mitä tahansa muuta funktiota. Koska UDF toimii natiivikoodina palvelinprosessin sisällä, se sopii raskaaseen laskentaan – mutta virhe yhdessä voi kaataa palvelimen, joten UDF-funktioita käytetään paljon harvemmin kuin tallennettuja funktioita.
Sisäänrakennetut vs. tallennetut vs. käyttäjän määrittämät funktiot: kumpaa kannattaa käyttää?
Kaikki kolme perhettä palauttavat yhden arvon ja niitä voidaan kutsua mistä tahansa SQL-lausekkeesta, mutta ne eroavat toisistaan siinä, kuka ne kirjoittaa, missä ne suoritetaan ja kuinka paljon riskejä ne kantavat. Alla oleva taulukko tiivistää nämä erot.
| Kriteeri | Sisäänrakennetut toiminnot | Tallennetut toiminnot | Käyttäjän määrittämät funktiot (UDF:t) |
|---|---|---|---|
| Kuka sen kirjoittaa | Toimitetaan MySQL | Sinä SQL:ssä | Sinä, C-kirjaimella tai C++ |
| Missä se asuu | Palvelimen sisällä | Tietokannan sisällä, luotu CREATE FUNCTION -funktiolla | Palvelimen lataama käännetty jaettu kirjasto |
| Tyypillinen käyttö | Muotoilu, matematiikka, aggregointi | Uudelleenkäytettävät liiketoimintasäännöt, kuten erääntynyt sekki | CPU-raskasta tai erikoistunutta logiikkaa SQL ei pysty ilmaisemaan |
| Pääriski | Ei eristetty | Hidas, jos kutsutaan rivi riviltä suuren taulukon yli | Kirjaston kaatuminen voi kaataa palvelimen |
Nykyään kannattaa aloittaa sisäänrakennetulla funktiolla. Jos mikään funktio ei sovi, kirjoita tallennettu funktio, jotta sääntö on yhdessä paikassa. Käytä UDF-funktiota vain, jos tallennettu funktio on mitattavasti liian hidas.

