Datatypen (MySQL)

Uit De Vliegende Brigade
Versie door Jeroen Strompf (overleg | bijdragen) op 23 feb 2021 om 11:09 (→‎Tekstveld)
(wijz) ← Oudere versie | Huidige versie (wijz) | Nieuwere versie → (wijz)
Naar navigatie springen Naar zoeken springen

Boolean

Datatypen bool, boolean en tinyint(1) zijn synoniem.

LET OP: Gebruik van een numeriek veldtype (bv. tinyint(1)) kan problematisch zijn bij import: Een leeg veld (dus "", niet NULL) krijgt namelijk waarde 0. Daardoor introduceer je fouten.

Tekstveld

  • tinytext(1)
  • Negatieve waarde: "0" of "-1" (liever niet gebruiken)
  • Positieve waarde: "1"
  • Ongedefineerde waarde: Alle overige velden. Dus ook bv. "2" betekent ongedefineerd. Bijvoorkeur vervang ik alle ongedefineerde waardes door NULL, om verwarring te vermijden.

Numeriek veld

Mocht ik tinyint(1) gebruiken, dan zijn dit de logische waardes: 

Datatype     Waarde   Betekenis
----------   ------   ---------
tinyint(1)   0        Niet waar
tinyint(1)   1        Waar

Dit zijn getallen (integers). Geen symbolische waardes of tekstwaardes. Op het moment dat ik een tekstveld gebruik (bv. varchar(40)-veld ivm. vermelden kolomtitel), gebruik ik ook 0 en 1 - Vaak kun je daar moeiteloos mee rekenen. 

A value of zero is considered false. Nonzero values are considered true:

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

The last two statements display the results shown because 2 is equal to neither 1 nor 0.

Conversion Rate

Een wisselkoers is duidelijk een decimal datatype:

  • Decimal(10,2) - Leuk begin, maar misschien te weinig cijfers voor de komma →
  • Decimal(10,4) - 6 cijfers voor de komma, 4 cijfers achter de komma - Amazon hanteert vier cijfers achter de komma
  • Op bankafschriften worden soms meer cijfers getoond, toch? ⇒ 4 Is voldoe
  • Momenteel is de zwakste valuta (zomer 2018), de Iraanse rial [1]. Op de zwarte markt is je euro zo'n 1,1 mln rial waard ⇒ Om een miljoen euro te kunnen weergeven in rial, heb je 12 cijfers voor de komma nodig ⇒ Niet nodig ⇒ decimal(10,4) is prima.

Datums

Bv. 

alter table tmp

   add column date_window_begin   date,
   add column date_window_end     date;

update tmp
set
    date_window_begin=		"2016-08-09",
    date_window_end=		"2018-07-31";   # YYYY-MM-DD

Zie verder https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html

Decimal

Ook voor geldbedragen en percentagen. Bv. 

 decimal (5,2)    # 5 posities  - 3 voor de komma, 2 achter - Tot        999,99
 decimal (10,2)   # 10 posities - 8 voor de komma, 2 achter - Tot 99.999.999,99
 decimal (10,4)   # 10 posities - 6 voor de komma, 4 achter - Tot    999.999,9999

Geldbedragen

Wat ik standaard gebruik: 

decimal(10,2)

Oftewel: 8 Cijfers voor het decimaalscheidingstekens en 2 cijfers na het decimaalscheidingsteken.

Het is aanlokkelijk om meer cijfers achter het decimaalscheidingsteken te hebben, maar het is rete-irritant bij verwerking van data, want dataconversie nodig, waarschuwingen, etc. - Niet doen!

Integere datatypes


  Kind          Storage   Signed                                                    Unsigned
  -----------   -------   -------------------------------------------------------   --------------------------------
* tinyint       1 byte    -128 to +127                                              0 to 255
* smallint      2 bytes   -32,768 to +32,767                                        0 to 65,535
* mediumint     3 bytes   -8,388,608 to 8,388,607                                   0 to 16,777,215
* int/integer   4 bytes   -2,147,483,648 to +2,147,483,647                          0 to 4,294,967,295
* bigint        8 bytes   -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807   0 to 18,446,744,073,709,551,615

Vaak (altijd?) kun je een waarde tussen haakjes meegeven, bv. 

tinyint(4)

Dit verandert niets aan het datatype, maar alleen iets aan de weergave. Je kunt dus nog steeds geen waarde van 1234 toevoegen aan een tinyint(4), want het maximum is nog steeds 255 of 128.

Fixed-point datatypes

Fixed-point datatypes zijn voor exacte rationele getallen. Er wordt dus niet ergens afgerond ofzo

  • Keywords: DECIMAL & NUMERIC
  • Voor geldbedragen en gewichten gebruikte ik decimal (10,4) - 6 cijfers vóór de komma, 4 cijfers achter de komma, maar ik ben overgestapt op decimal (8,2) - Geen gedoe met extra cijfers achter de komma

Floating point-datatypes

longtext, mediumtext, tinytext, text

Veldlengtes [2] zijn gedefineerd in bytes en niet in het aantal karakters. Het aantal karakters waarmee dit overeenkomt, hangt af van de gekozen tekencodering. In de praktijk verschilt dit in Latin1 niet, en in UTF8 zo'n 10%: 


      Type | Maximum length (bytes)               | Maximum length (characters, approx.) |
-----------+--------------------------------------+--------------------------------------+
  TINYTEXT |           255 (2 8−1) bytes          | 225 - 255                            |
      TEXT |        65,535 (216−1) bytes = 64 KiB | 59.000 - 65.535                      |
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB | 14.3 mln - 16.777.215                |
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB | 3.8 mld - 4,294,967,295              |

mediumtext

  • MySQL gebruikt mediumtext regelmatig bij impliciete velddefinnities voor diverse vrij lange teksten die ik gebruik voor exportbestanden
  • Merk op dat mediumtext langer is dan text!

text

Max. 65.536 bytes. Voorbeeld: 

ALTER TABLE `dwh`.`root` 
ADD COLUMN `note` text(65530) NULL AFTER `country_of_origin`;

Tekstvelden kun je niet op dezelfde manier indexeren als bv. varchar-velden: Je moet de sleutellengte meegeven. Ik denk dat als ik in de situatie ben, dat ik een tekst-veld wil indexeren, dat ik me moet afvragen of txt wel het goede veldtype is.

Percentages

Gebruik decimal [3] en sla percentages op als decimale breuk, bv.: 

* 0,01% ⇒ 0.0001
* 1,26% ⇒ 0.0126
* 1%    ⇒ 0.01
* 10%   ⇒ 0.1
* 100%  ⇒ 1
* 1000% ⇒ 10

Nauwkeurigheid hangt af van zowel het aantal cijfers achter de komma, als voor de komma: Je bent immers niet beperkt tot 100%.

Voorbeeld(en): 

 decimal (5,4)    # 5 posities - 1 voor de komma, 4 achter, bv. 1.26% tot 100%, bv. conversiepercentages
 decimal (10,6)   # 10 posities - 4 voor de komma, 6 achter de komma → Standaard bij importeren Amazon-rapportages in ''raw'' format

Tinyint

[-128:128] of [0:255]

Voorbeelden:

  • Gewicht koolborstels in grammen (afgerond op hele grammen)
  • Boolean: tinyint(1)

Let op: Je kunt een waarde tussen haakjes meegeven, bv. 

tinyint(4)

Dit verandert niets aan het datatype, maar alleen iets aan de weergave. Je kunt dus nog steeds geen waarde van 1234 toevoegen aan een tinyint(4), want het maximum is nog steeds 255 of 128.

Tinytext vs. varchar

Zie dit ingewikkelde verhaal. Ik houd het op varchar

Valuta

Gebruik decimal. Bv.: 

 decimal (5,2)    # 5 posities  - 3 voor de komma, 2 achter - Tot        999,99
 decimal (10,2)   # 10 posities - 8 voor de komma, 2 achter - Tot 99.999.999,99   ← Gebruik ik meestal
 decimal (10,4)   # 10 posities - 6 voor de komma, 4 achter - Tot    999.999,9999

Varchar

Varchar-velden zijn indexbaar (itt. txt-velden) en kunnen tot ca. 65.536 bytes bevatten. Voorbeeld: 

create table tmp7
(
   blub varchar(65000) # Geen probleem
);

Dit geeft een waarschuwing: 

create table tmp6
(
   blub varchar(100000) # Waarschuwing: Converting column 'blub' from VARCHAR to TEXT
);

Zoektermen

  • Datatypes
  • Data types
  • Object types

Zie ook

Bronnen

dev.mysql.com

Tekst-formaten

Valuta

Overig

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Datatypen_(MySQL)&oldid=25452"