Last updated September 30, 2012. Created by bjaspan on July 16, 2007.
Edited by andreathegiant, meustrus, cato, xjm. Log in to edit this page.
The following table shows all the legal combinations of the 'type' and 'size' fields of a column specification along with the underlying database data types used by each combination. As of Drupal 7, MySQL, PostgreSQL, and SQLite data types are supported in Drupal core. Drupal 6 core supports MySQL and PostgreSQL.
The maximum size or range of each underlying type is also shown. For integer types, the number of storage bytes is given; the maximum range depends on whether the field is signed or unsigned. For varchar, text, and blob types, the maximum size is given.
| 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)1 | 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 |
| datetime2 | normal2 | datetime, years 1001 CE to 9999 CE2 | timestamp, years 4713 BCE to 5874897 CE2 | (UNSUPPORTED)2 |
1. MySQL 5.0.3 or later (see http://dev.mysql.com/doc/refman/5.0/en/char.html).
PostgreSQL 9.0 or later (see http://www.postgresql.org/docs/9.0/static/datatype.html).
SQLite 3.0 or later (see http://www.sqlite.org/datatype3.html).
2. Note that Datetime support was removed from D7 db api - use mysql_type or pgsql_type if you want this functionality
Comments
For numeric fields, you must
For numeric fields, you must specify both precision and scale to generate the field.
For example, to create the equivalent to the MySql statement
`price` DECIMAL(10, 2) NOT NULL DEFAULT 0,use the following
'price' => array('type' => 'numeric', 'size' => 'normal', 'not null' => TRUE, 'default' => 0, 'precision' => 10, 'scale' => 2)Alan Davison
Back roads somewhere in South America
Custom types
Per Barry, at http://groups.drupal.org/node/7558#comment-26055, to add a custom type not already defined by schema API, like geospacial fields, you define your schema like the following, providing the right 'real' field type for alternative databases:
<?php$schemea['table']['fields']['foo'] = array(
'type' => 'varchar',
'mysql_type' => 'MAGIC_GEO_TYPE',
'pgsql_type' => 'whatever',
);
?>
Custom types
Exactly put. But be very weary of portability. Thoroughly document the custom type to allow the application portability with specific database types of the next database type, (MySQL, MSSQL, PostGres, mSQL etc).
David K Hill
Chief Information Officer
Greater Sacramento Area Masonic Service Bureau
Chief Financial Officer
Masonic Hall Association of Wheatland
Geospatial data column module?
Now that mysql supports geospatial column types, is there a module already started to add the geospatial column type to the drupal schema? I do not want to duplicate someones work :) Thanks.
Problem with storing dates in Drupal Schema API
source: http://www.clusterdigital.co.uk/blog/09/09/storing-dates-drupal-schema-api
I also ran into this problem.
I also ran into this problem. Get a lot of notices on the "type" key.
I think it's really crappy to store dates as timestamp. However if this solves my situation, I'm happy.
Try using strtotime()
You could use the MySQL datetime column type (which is preferable as it's easier to debug in the database and code) and just call the PHP function strtotime() on the resulting DB -formatted string to get a Unix-style timestamp, and it should parse out just fine...
----------
"Nothing is impossible - it just hasn't been done yet."
Phase2 Technology
text / normal in MySQL
Drupal "text / normal" become "text" type in MySQL. According to the documentation "A TEXT column with a maximum length of 65,535 (216 – 1) characters." ( http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html ) this is not 16 KB, but 64 KB.
I think this is a typo here.
Serial vs int
Just a side note in case anyone wonders what the difference is - serial and int are the same internal column types, but serial is also set to be auto-increment, whereas int is not.
In other words, use serial for things like row IDs and int for columns that reference those IDs (or nid, vid, lid, etc) in other tables.
----------
"Nothing is impossible - it just hasn't been done yet."
Phase2 Technology
Few useful datatypes missing
I am not sure why you decided to introduce this mapping. Would it not be easier for the schema to use the exact types in MySQL or Postgresql without this translation in which things get lost?
Simple solution:
schema['mytable', 'mysql'] => array(.....);schema['mytable', 'postgres'] => array(....);
//or to take things further
schema['mytable', 'mssql'] => array(....);
schema['mytable', 'ora'] => array(....);
The way it's done now, it's missing a lot of types that are built-in to each db engine. And I'm not talking about some weird strange types. I am talking about simple, elementary things like bit (bool, boolean) or date or time.
This is a huge limitation and I would hope to see it solved very soon although I know that's not possible.
I started with nothing and still got most of it.
Why not SQL Specific Datatypes - Simple Answer
From what I am seeing in the reasoning behind the use of "Drupal" data types rather than "SQL" data types appears to be concept that will enable Drupal to remain Database Independent. If we define the data types to correspond and behave like the native database, then Drupal becomes Database Concentric and quickly loses it's portability.
In other words devising an application that is locked to the basic underpinnings of the database brand prevents us from creating applications that are easily used on different database platforms.
In my humble opinion.
David K Hill
Chief Information Officer
Greater Sacramento Area Masonic Service Bureau
Chief Financial Officer
Masonic Hall Association of Wheatland
Datetime is gone for Drupal 7
Please note that according to http://drupal.org/node/866340, the datetime type has been deleted for Drupal 7. I pulled my hair out trying to get my module.install file to work until I found this. Unfortunately, I could not find a database-independent way to define the field, so I replaced these two lines:
'type' => 'datetime', //The Drupal 6 way'size' => 'normal',
with these two lines:
'mysql_type' => 'datetime', // The equivalent MySQL data type'pgsql_type' => 'timestamp', // The equivalent PostgreSQL data type
Thanks for posting
I was having the same problem and your post helped me to solve it.
Thanks from me also
Was pulling my hair out why datetime wasn't working in 7. Peculiar....
yikes that should be updated
yikes that should be updated in the docs..
Also spent too much time
Also spent too much time deciphering this. Could this be non-intuitive exception be highlighted in the documentation, please...
------
Tomáš J. Fülöpp @vacilando.is
Thanks.
Thanks.
From the date module
From the date module
$db_columns['value'] = array('type' => 'datetime',
'mysql_type' => 'DATETIME',
'pgsql_type' => 'timestamp without time zone',
'sqlite_type' => 'VARCHAR',
'sqlsrv_type' => 'smalldatetime',
'not null' => FALSE,
'sortable' => TRUE,
'views' => TRUE,
);
This assumes storage in UTC
Alan Davison
Back roads somewhere in South America
date
For a straight date, I use the following. Have not researched the equivalents outside of MySQL.
'date' => array('type' => 'date',
'mysql_type' => 'DATE',
'pgsql_type' => 'timestamp without time zone',
'sqlite_type' => 'VARCHAR',
'sqlsrv_type' => 'smalldatetime',
),
petermoulding.com/web_architect
Ohwow, if THAT really is what
Ohwow, if THAT really is what "DB independent" is supposed to mean... my good lord.
Is that serious? Wouldn't a true DB abstraction layer care for the necessary workarounds if a global datatype is not available in a certain DB backend? If I really do have to continue caring whether some DB API syntax works in a backend or not, then there is still a long way to go and the denotation "abstraction" is useless. Feels a bit like OOP where you are forced to edit private vars (if it were possible). Or where you have to query an object for its internals to make sure you may call a certain method.
Or am I getting something completely wrong here? Also, if not: Where is the best place to hook in and help this getting solved in favor of a *real* DB abstraction layer?
Just ranting because I was really happy thinking we'd already passed these hindering 'if ($db_type == 1) {} elseif (){}' stuff. But sounds like we didn't :'(
Edit: OK, for datetime, it is not considered a globally available type. My fault. But what about char? It would have to be removed as well - or is that just a documentation lack?
abstraction != isolation
The abstraction is not *that* horrible. For example, you have a generic type "date" so that CRUD operations can be translated from code into SQL. You have some database / vendor specific mappings when defining the schema, and this is all declarative so there is no "if type" nonsense you suppose. You do it once, then... you use it in your code, and (in theory) in just works.
Honestly this pales in comparison to other issues in database design. Try supporting a truly I18N website with users across different timezones. Lots of fun.
Let's also keep in mind, this is not a fully blown ORM. (one can dream).
Specific ./. abstraction
I did not say that vendor specific setups should be punished, there will always be some specific syntax someone will need for some special task. But abstraction is all or nothing. Either I do have a 100% reliable standard API so that I need no longer worry about databases or I can drop abstraction, I don't see any space for relativation there. If I explicitely want to design a module for e.g. a proprietary MSQL task, I can still do that but then I will use a specified db_query anyway, and not an API function like db_select etc.
The basic idea of the abstraction is actually really not to think about switches and fallbacks, that's literally expressed somewhere[tm] in the docs, as is the idea that I can implement API calls today which will even work if someone someday contributes a startrek_db or whatever_db interface. At least "char" is clearly breaking that up, and that even without being inevitable btw.
no Schema type for mysql type datetime
Thanks this solved my problem!
Thanks
Thanks
Text length/size is mandatory when declaring a text field
length/size is mandatory when declaring a text field.
For length/size, use these options -> tiny, small, medium, normal, big.
Just adding this in here for an extra note for anyone that needs it. Thanks.