Oppaat: MySQL ja PHP: Osa 7 - Tietotyypit

Tavallisimmat tietokannan kenttien tietotyypit ovat lukuarvot ja merkkijonot, joita on käytetty luontevasti opassarjan alusta alkaen. Tässä oppaassa opimme lisää asioita tietotyypeistä ja tutustumme lisäksi päivämäärien käsittelyyn.

Tietotyypit

Taulun luonnissa jokaiselle kentälle annetaan tietotyyppi ja mahdollisesti muita määrityksiä. Tietotyyppi tarkoittaa, millaista tietoa kenttään voi tallentaa: tavalliset vaihtoehdot ovat lukuarvo, merkkijono ja aika-arvo.

Esimerkiksi taulun tuotteet luonnissa esiintyvät tietotyypit INT ja TEXT sekä muut määritykset PRIMARY KEY ja AUTO_INCREMENT:

CREATE TABLE tuotteet (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nimi TEXT,
    hinta INT
);

Seuraavassa taulukossa on tärkeitä MySQL:n tietotyyppejä:

Lukuarvot

Lukuarvot merkitään kyselyihin sellaisenaan. Jos luvussa on desimaaliosa, erotinmerkki on piste. Lukuarvoja ovat esimerkiksi 35, -9 ja 14.22.

Kokonaislukutyyppi INT on 32-bittinen, ja sen pienin arvo voi olla -2147483648 ja suurin arvo voi olla 2147483647. Jos tämä ei riitä, voi käyttää 64-bittistä tyyppiä BIGINT, jonka pienin arvo voi olla -9223372036854775808 ja suurin arvo voi olla 9223372036854775807.

Lisämerkintä UNSIGNED tarkoittaa, että käytössä ei ole negatiivisia lukuja mutta positiivisia lukuja on kaksinkertainen määrä.

Desimaalilukutyypin DECIMAL määrittelyssä annetaan numeroiden kokonaismäärä ja desimaaliosan numeroiden määrä. Esimerkiksi DECIMAL(5, 2) tarkoittaa, että luvussa voi olla yhteensä 5 numeroa ja desimaaliosassa voi olla 2 numeroa.

Merkkijonot

Merkkijonot merkitään kyselyihin heittomerkkien tai lainausmerkkien sisään. Tässä opassarjassa käytetään aina heittomerkkejä. Merkkijonoja ovat esimerkiksi 'ABC', '12345' ja 'MySQL'. Jos merkkijonossa on heittomerkki, sen eteen täytyy merkitä kenoviiva: 'vaa\'at'.

Merkkijonotyyppi TEXT voi sisältää 65535 merkkiä. Jos tämä ei riitä, voi käyttää tyyppejä MEDIUMTEXT ja LONGTEXT, jotka voivat sisältää vastaavasti 16777215 (yli 16 miljoonaa) ja 4294967295 (yli 4 miljardia) merkkiä.

Aika-arvot

Tyyppi DATETIME sisältää päivämäärän ja kellonajan, minkä lisäksi DATE sisältää vain päivämäärän ja TIME sisältää vain kellonajan. Päivämäärän vuoden täytyy olla ainakin 1000 ja korkeintaan 9999.

Päivämäärien merkintätapa on 'VVVV-KK-PP', ja kellonaikojen merkintätapa on 'TT:MM:SS'. Esimerkiksi päivämäärä 24.6.2009 merkitään '2009-06-24' ja kellonaika 9:15:20 merkitään '09:15:20'.

Tämän oppaan lopussa on esimerkkejä aika-arvojen käsittelystä.

Tyhjät kentät

Jos uuden rivin lisäyksessä jollekin kentälle ei anna arvoa, kenttä jää tyhjäksi ja sen arvona on NULL. Esimerkiksi seuraava kysely lisää tauluun tuotteet uuden rivin, jonka kenttä hinta jää tyhjäksi:

INSERT INTO tuotteet (nimi) VALUES ('punajuuri');

Seuraava kysely hakee taulusta rivit, joille ei ole annettu hintaa:

SELECT * FROM tuotteet WHERE hinta IS NULL;

+----+-----------+-------+
| id | nimi      | hinta |
+----+-----------+-------+
|  7 | punajuuri |  NULL |
+----+-----------+-------+

Muut määritykset

Tietotyypin lisäksi kentälle voi antaa muiden muassa seuraavia määrityksiä:

Esimerkki: kalenteri

Aika-arvojen käsittely muistuttaa paljon lukuarvojen ja merkkijonojen käsittelyä. Seuraavassa esimerkissä luodaan kalenteri, johon voi tallentaa merkintöjä päivämäärän ja kellonajan mukaan.

Luodaan kalenteria varten taulu kalenteri, jossa kentät viesti ja aika sisältävät kalenterimerkinnän viestin ja ajankohdan.

CREATE TABLE kalenteri (
    id INT PRIMARY KEY AUTO_INCREMENT,
    viesti TEXT,
    aika DATETIME
);

Lisätään kalenteriin neljä merkintää:

INSERT INTO kalenteri (viesti, aika) VALUES ('palaveri', '2009-06-24 14:00');
INSERT INTO kalenteri (viesti, aika) VALUES ('konsertti', '2009-06-24 18:00');
INSERT INTO kalenteri (viesti, aika) VALUES ('tennis', '2009-06-27 16:30');
INSERT INTO kalenteri (viesti, aika) VALUES ('palaveri', '2009-06-29 14:00');

Taulun sisältö on nyt seuraava:

+----+-----------+---------------------+
| id | viesti    | aika                |
+----+-----------+---------------------+
|  1 | palaveri  | 2009-06-24 14:00:00 |
|  2 | konsertti | 2009-06-24 18:00:00 |
|  3 | tennis    | 2009-06-27 16:30:00 |
|  4 | palaveri  | 2009-06-29 14:00:00 |
+----+-----------+---------------------+

Ajan esitystapa

Oletuksena tulostaulussa oleva päivämäärä esitetään muodossa VVVV-KK-PP ja kellonaika esitetään muodossa TT:MM:SS. Funktion DATE_FORMAT avulla esitystavan voi päättää itse. Seuraava kysely hakee päivämäärän muodossa P(P).K(K).VVVV ja ajan muodossa T(T):MM:

SELECT DATE_FORMAT(aika, '%e.%c.%Y %k:%i') FROM kalenteri;

+-------------------------------------+
| DATE_FORMAT(aika, '%e.%c.%Y %k:%i') |
+-------------------------------------+
| 24.6.2009 14:00                     |
| 24.6.2009 18:00                     |
| 27.6.2009 16:30                     |
| 29.6.2009 14:00                     |
+-------------------------------------+

Ajan osat

Seuraava kysely hakee merkinnät, joissa päivämäärä on 24.6.2009:

SELECT * FROM kalenteri WHERE aika >= '2009-06-24' AND aika < '2009-06-25';

+----+-----------+---------------------+
| id | viesti    | aika                |
+----+-----------+---------------------+
|  1 | palaveri  | 2009-06-24 14:00:00 |
|  2 | konsertti | 2009-06-24 18:00:00 |
+----+-----------+---------------------+

Seuraava kysely hakee kesäkuun 2009 merkinnät:

SELECT DAYOFMONTH(aika), HOUR(aika), viesti
FROM kalenteri
WHERE YEAR(aika) = 2009 AND MONTH(aika) = 6;

+------------------+------------+-----------+
| DAYOFMONTH(aika) | HOUR(aika) | viesti    |
+------------------+------------+-----------+
|               24 |         14 | palaveri  |
|               24 |         18 | konsertti |
|               27 |         16 | tennis    |
|               29 |         14 | palaveri  |
+------------------+------------+-----------+

Seuraava kysely hakee merkintöjen viikonpäivän ja viikon numeron:

SELECT WEEKDAY(aika), WEEK(aika, 1), aika FROM kalenteri;

+---------------+---------------+---------------------+
| WEEKDAY(aika) | WEEK(aika, 1) | aika                |
+---------------+---------------+---------------------+
|             2 |            26 | 2009-06-24 14:00:00 |
|             2 |            26 | 2009-06-24 18:00:00 |
|             5 |            26 | 2009-06-27 16:30:00 |
|             0 |            27 | 2009-06-29 14:00:00 |
+---------------+---------------+---------------------+

Tässä luku 0 tarkoittaa maanantaita, luku 1 tarkoittaa tiistaita jne.

Nykyinen aika

Funktio NOW kertoo kyselyn suoritushetken ajankohdan. Oletetaan seuraavaksi, että kyselyt suoritetaan 25.6.2009 kello 20:23, jolloin funktio NOW palauttaa arvon '2009-06-25 20:23:00'.

Seuraava kysely hakee kaikki tulevaisuudessa olevat merkinnät:

SELECT * FROM kalenteri WHERE aika > NOW();

+----+----------+---------------------+
| id | viesti   | aika                |
+----+----------+---------------------+
|  3 | tennis   | 2009-06-27 16:30:00 |
|  4 | palaveri | 2009-06-29 14:00:00 |
+----+----------+---------------------+

Seuraava kysely lisää uuden merkinnän juuri tälle hetkelle:

INSERT INTO kalenteri (viesti, aika) VALUES ('nyt', NOW());

Tämän kyselyn jälkeen taulun sisältö on seuraava:

+----+-----------+---------------------+
| id | viesti    | aika                |
+----+-----------+---------------------+
|  1 | palaveri  | 2009-06-24 14:00:00 |
|  2 | konsertti | 2009-06-24 18:00:00 |
|  3 | tennis    | 2009-06-27 16:30:00 |
|  4 | palaveri  | 2009-06-29 14:00:00 |
|  5 | nyt       | 2009-06-25 20:23:00 |
+----+-----------+---------------------+