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.

  • 🔤 Merkkijonofunktiot: UCASE, LCASE ja CONCAT muokkaavat tekstiä kyselyn aikana; aliasoivat lasketun sarakkeen AS:llä, jotta tulosjoukolla on luettava otsikko.
  • 🔢 Numeerinen Operatorit: DIV suorittaa kokonaislukujakolaskun, / palauttaa desimaaliosamäärän ja % (tai MOD) palauttaa jakolaskun jakojäännösosan.
  • 📅 Päivämääräfunktiot: DATE_FORMAT muuntaa tallennetun VVVV-KK-PP-arvon miksi tahansa näyttömuotoon, kuten %d-%m-%Y, muuttamatta yhtäkään sovelluskoodiriviä.
  • 🛠️ Tallennetut funktiot: CREATE FUNCTION rekisteröi uudelleenkäytettävän logiikan palvelimen sisälle; määritä se NOT DETERMINISTIC -funktioksi aina, kun runko kutsuu CURDATE()- tai NOW()-funktiota.
  • ⚙️ Käyttäjän määrittämät funktiot: C-kielellä kirjoitetut ulkoiset rutiinit C++ käännetään palvelimelle ja toimivat sitten täsmälleen kuten natiivifunktiot.
  • 🚀 Vaikutus suorituskykyyn: Laskelmien syöttäminen tietokantaan poistaa päällekkäisen logiikan jokaisesta asiakassovelluksesta ja vähentää verkon edestakaisia ​​matkoja.

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.

Miksi käyttää MySQL Tehtävät

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.

UKK

Funktion on palautettava täsmälleen yksi arvo, ja sitä voidaan käyttää SELECT-, WHERE- tai ORDER BY -lausekkeen sisällä. Tallennettu proseduuri palauttaa nolla tai useita tulosjoukkoja, sitä ei voida upottaa lausekkeeseen, ja sitä kutsutaan CALL-lausekkeella.

Suorita DROP FUNCTION IF EXISTS sf_name ja luo se sitten uudelleen. MySQL sillä ei ole CREATE OR REPLACE FUNCTION -funktiota, ja ALTER FUNCTION muuttaa vain ominaisuuksia, kuten kommenttia tai suojaustyyppiä, ei koskaan itse funktion runkoa.

Ne voivat. WHERE-lausekkeen indeksoidun sarakkeen ympärille kiedottu funktio estää MySQL käyttämästä kyseistä indeksiä, pakottaen täyden skannauksen. Suodata raakasarakkeen perusteella ja käytä funktiota vain SELECT-luettelossa.

Kyllä. Tekoälyavustajat voivat luonnostella CREATE FUNCTION -koodia selkokielisestä säännöstä. Tarkista aina luodun rungon oikeat DETERMINISTIC-ominaisuudet, NULL-käsittely ja parametritietotyypit ennen sen suorittamista tuotantopalvelimella.

Ei. Tekoälymallit voivat keksiä funktioiden nimiä, ohittaa NULL-tapaukset tai jättää huomiotta versioerot. Testaa jokainen luotu funktio datan kopiolla ja vahvista tulokset itse kirjoittamaasi ja varmentamaasi kyselyä vasten.

Tiivistä tämä viesti seuraavasti: