4

Hi I'm working on my first sqlite project and I was just wondering if sqlite supports all the same field type that MYsql does e.g - DATETIME, TINYINT, VARCHAR ect.

If sqlite supports limited field type it would be a big help if someone could provide me with them.

thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adam
  • 2,632
  • 8
  • 34
  • 60
  • First result on google for "sqlite datatypes": http://www.sqlite.org/datatype3.html – Sean Bright Jan 25 '10 at 16:47
  • read this link i think it is similar to your question :) http://stackoverflow.com/questions/1942586/comparison-of-database-column-types-in-mysql-postgresql-and-sqlite-cross-map – raman rayat Jan 05 '14 at 22:06
  • read this one might be it help you :) http://stackoverflow.com/questions/1942586/comparison-of-database-column-types-in-mysql-postgresql-and-sqlite-cross-map – raman rayat Jan 05 '14 at 22:08

3 Answers3

4

sqlite has a very limited number of datatypes. According to the Documentation it has the following 5 datatypes:

  • NULL. The value is a NULL value.
  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  • BLOB. The value is a blob of data, stored exactly as it was input.

So to answer your question, no, sqlite and mysql do not have the same datatypes.

Marius
  • 57,995
  • 32
  • 132
  • 151
3

Old thread, but I think the poster was asking for a map between mySQL and SQLite data types. Since this article came up high on Google when I was searching for same, here's a link I found which more specifically answers the poster's question (YMMV):

http://drupal.org/node/159605

calenti
  • 175
  • 1
  • 8
0

Sorry my english;

SQLite has 5 "Class Storage" that is more more general than a datatype: NULL, INTEGER (1 to 8 bytes), REAL (8-bytes, float point), TEXT (strings) and BLOB (or type/class no defined).

Others datatypes was defined using a logic to define the "Affinity". This affinity is used to "convert" any type to only theses 5 classes.

Sqlite see the datatype in the column; Match the affinity; See the value informed; If necessary do the convertion, anda save the value. For example, TINYCHAR has affinity with TEXT, then will be saved as TEXT.

If you understand the logic, you will understand the bridge between datatypes. "3,14" can be saved as REAL or TEXT

SqLite look for strings in informed datatype when coumun/table was created.

If there are "INT" in the datatype informed (tinyint, smalint, integer, or any word with "INT") the value will be storage as INTEGER. If the value was a text ("10") will be converted to numeber (10).

If there are not "INT" in the name of datatype, then Sqlite will look for "CHAR", "CLOB", or "TEXT" expressions. If is finded, the value will be storage as "TEXT". If Value was a number, will be converted to text.

If there are not "INT", "CHAR", "CLOB", or "TEXT" in the Datatype name, then Sqlite will look for "BLOB" expression. If "BLOB"was finded, the value will be storaged as NONE (whithout type defined).

If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column will be REAL

If any teste above was true, will be storaged as NUMERIC (number with float point). If the value was a string, sqlite will attemp tro convert. If was a integer ou string representing a integer, will be saved as float point. (there are some optimization in this case.

For Date and Time there are not specift types, byt, there are some functions to handle the value. If value was entering as: TEXT = as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). REAL =as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. INTEGER = as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

MiguelAGP
  • 11
  • 1