Drupal Mysql / Database Data types | Drupal 8

Drupal Mysql / Database Data types

Submitted by editor on Mon, 12/07/2015 - 19:27

Link : https://www.drupal.org/node/159605

Note : that Datetime support was removed from D7 db api - use mysql_type or pgsql_type if you want this functionality.
Drupal use 'int' for the date/datetime, like:
      'date' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),

If you are using VARCHAR, you must specify the length (Ex : 'length' => 255)

type size MySQL type & size/range PostgreSQL type & size/range SQLite type
serial tiny tinyint, 1 B serial, 4 B integer
serial small smallint, 2 B serial, 4 B integer
serial medium mediumint, 3 B serial, 4 B integer
serial big bigint, 8 B bigserial, 8 B integer
serial normal int, 4 B serial, 4 B integer
int tiny tinyint, 1 B smallint, 2 B integer
int small smallint, 2 B smallint, 2 B integer
int medium mediumint, 3 B int, 4 B integer
int big bigint, 8 B bigint, 8 B integer
int normal int, 4 B int, 4 B integer
float tiny float, 4 B real, 6 digits float
float small float, 4 B real, 6 digits float
float medium float, 4 B real, 6 digits float
float big double, 8 B double precision, 15 digits float
float normal float, 4 B real, 6 digits float
numeric normal numeric, 65 digits numeric, 1000 digits numeric
varchar normal varchar, 255 B (D6) or 64 KB (D7 and later) varchar, 1 GB varchar
char normal char, 255 B character, 1 GB (UNSUPPORTED)
text tiny tinytext, 256 B text, unlimited text
text small tinytext, 256 B text, unlimited text
text medium mediumtext, 16 MB text, unlimited text
text big longtext, 4 GB text, unlimited text
text normal text, 16 KB text, unlimited text
blob big longblob, 4 GB bytea, 4 GB blob
blob normal blob, 16 KB bytea, 4 GB blob
datetime normal datetime, years 1001 CE to 9999 CE timestamp, years 4713 BCE to 5874897 CE (UNSUPPORTED)

 

Mysql integer minimum and maximul values

Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT. The following table shows the required storage and range for each integer type. If toy have an error like Numeric value out of range: 1264 Out of range value for column {A COILUMN NAME} at row.... Please check this table.

Type Storage Minimum Value Maximum Value
  (Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
    0 255
SMALLINT 2 -32768 32767
    0 65535
MEDIUMINT 3 -8388608 8388607
    0 16777215
INT 4 -2147483648 2147483647
    0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
    0 18446744073709551615

Ref : https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Add new comment