比较MySQL，PostgreSQL和SQLite中的数据库列类型？ （交叉映射） [英] Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping)
问题描述
我想寻找一些方法，在最常用的数据库中关联列类型： MySQL ， PostgreSQL 和 SQLite 。
这里是我到目前为止，但恐怕还没有完成，我需要一些有更多经验的人帮助我完成任何缺少的类型。
MySQL PostgreSQL SQLite
TINYINT SMALLINT INTEGER
SMALLINT SMALLINT
MEDIUMINT INTEGER
BIGINT BIGINT
BIT BIT INTEGER
_______________________________________________________
TINYINT未分配的SMALLINT整数
SMALLINT未分配的整数
MEDIUMINT未分配的整数
INT未分配的
$ b BIGINT UNSIGNED NUMERIC（20）
_______________________________________________________
DOUBLE DOUBLE PRECISION REAL
FLOAT REAL REAL
DECIMAL DECIMAL REAL
NUMERIC NUMERIC REAL
_______________________________________________________
BOOLEAN BOOLEAN整数
_______________________________________________________
日期日期文本
时间时间
时间时间
_______________________________________________________
TIMESTAMP DEFAULT TIMESTAMP DEFAULT TEXT
NOW（）NOW（）
_______________________________________________________
LONGTEXT TEXT TEXT
MEDIUMTEXT TEXT TEXT
BLOB BYTEA BLOB
VARCHAR VARCHAR TEXT
CHAR CHAR TEXT
_______________________________________________________
columnname INT columnname SERIAL INTEGER PRIMARY
AUTO_INCREMENT KEY AUTOINCREMENT
我会做不同的事情列表：
MySQL中的MEDIUMINT是一个奇怪的鸭子（3字节）。我会避免它，否则将其映射到INTEGER。
MySQL BOOLEAN（别名BOOL，别名TINYINT（1））与pg布尔类型不兼容。您可能会也可能不能移植应用程序，取决于它们用作布尔文字。在MySQL中，TRUE和FALSE映射到1和0整数值。它看起来像pg BOOLEAN类型使用字符串字面符号。因此，应用程序可能是可移植的，也可能不是可移植的 - 至少它不会丢失替换。
最后，对于tabl的最后一行，我认为SQLite短语应该读：
INTEGER PRIMARY KEY AUTOINCREMENT
这大致相当于
BIGINT PRIMARY KEY AUTO_INCREMENT
。在postgres中，SERIAL数据类型产生一个INTEGER列，并且与MySQL的
大致相同 INTEGER PRIMARY KEY AUTO_INCREMENT
Postgres也有一个BIGSERIAL类型，与SERIAL相同，但是使用BIGINT类型而不是INT类型
我缺少INTEGER（alias INT）。
为MySQL。它类似于INTEGER在pg。非常重要的省略：
VARCHAR和CHAR。语义上，MySQL和PG中的VARCHAR和MySQL和PG中的CHAR是相同的，但在MySQL中，这些类型的最大长度要短得多。在MySQL中，这些类型的最大值可能小于64kb，以pg 1Gb（字节）为单位。实际长度说明符以字符数表示，因此如果您有多字节字符集，则必须将最大长度除以最大字符数，以获得为该字符集指定的理论最大长度。在SQLite，VARCHAR和CHAR映射到TEXT
MySQL和PG中的BIT数据类型大致具有相同的语义，但在MySQL中，BIT数据类型的最大长度为64（位）
我认为MySQL VARBINARY数据类型与PG的BYTEA数据类型最相似。 （但是确实MySQL的BLOB类型也映射到）
MySQL中的FLOAT类型应该等同于postgres中的REAL（和SQLite中的REAL）
DECIMAL类型在MySQL中相当于postgres中的DECIMAL，除了在postgres中，类型不对精度施加arbtrary限制，而在MySQL中，最大精度为（我相信）70（即70个位置）
对于MySQL和Postgres，NUMERIC是DECIMAL类型的别名。
I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite.
Here is what I have so far, but I'm afraid it's not done and I need some people with more experience to help me finish any missing types.
MySQL PostgreSQL SQLite
TINYINT SMALLINT INTEGER
SMALLINT SMALLINT
MEDIUMINT INTEGER
BIGINT BIGINT
BIT BIT INTEGER
_______________________________________________________
TINYINT UNSIGNED SMALLINT INTEGER
SMALLINT UNSIGNED INTEGER
MEDIUMINT UNSIGNED INTEGER
INT UNSIGNED BIGINT
BIGINT UNSIGNED NUMERIC(20)
_______________________________________________________
DOUBLE DOUBLE PRECISION REAL
FLOAT REAL REAL
DECIMAL DECIMAL REAL
NUMERIC NUMERIC REAL
_______________________________________________________
BOOLEAN BOOLEAN INTEGER
_______________________________________________________
DATE DATE TEXT
TIME TIME
DATETIME TIMESTAMP
_______________________________________________________
TIMESTAMP DEFAULT TIMESTAMP DEFAULT TEXT
NOW() NOW()
_______________________________________________________
LONGTEXT TEXT TEXT
MEDIUMTEXT TEXT TEXT
BLOB BYTEA BLOB
VARCHAR VARCHAR TEXT
CHAR CHAR TEXT
_______________________________________________________
columnname INT columnname SERIAL INTEGER PRIMARY
AUTO_INCREMENT KEY AUTOINCREMENT
List of things I'd do differently:
MEDIUMINT in MySQL is an odd duck (3 bytes). I would avoid it, but otherwise map it to INTEGER too.
The MySQL BOOLEAN (alias BOOL, alias TINYINT(1) ) is not compatible with the pg boolean type. You may or may not be able to port apps depending on what they use as boolean literals. In MySQL, TRUE and FALSE map to 1 and 0 integer values. It looks like the pg BOOLEAN type uses string literal notation. So apps may or may not be portable - at least it is no drop in replacement.
Finally, for the last line in your tabl I think the SQLite phrase should read:
INTEGER PRIMARY KEY AUTOINCREMENT
This is roughly equivalent to
BIGINT PRIMARY KEY AUTO_INCREMENT
in MySQL. In postgres, the SERIAL datatype results in an INTEGER column, and this will about the same as MySQL's
INTEGER PRIMARY KEY AUTO_INCREMENT
Postgres also has a BIGSERIAL type, which is the same as SERIAL but with a BIGINT type instead of an INT type.
What I missed:
I am missing INTEGER (alias INT) for MySQL. It is comparable to INTEGER in pg. Very important omissions: VARCHAR and CHAR. Semantically, VARCHAR in MySQL and PG, and CHAR in MySQL and PG are the same, but in MySQL these types have a much shorter maximum length. In MySQL these types can have a maximum of a little less than 64kb, in pg 1Gb (bytes). The actual length specifier is expressed in the number of characters, so if you have a multi-byte character set, you have to divide the maximum lenght by the maximum number of characters to get the theoretical maximum length specified for that characterset. In SQLite, VARCHAR and CHAR map both to TEXT
The BIT datatypes in MySQL and PG have roughly the same semantics, but in MySQL the maximum length of the BIT data type is 64 (bits)
I think the MySQL VARBINARY data type is best comparable to PG's BYTEA datatype. (but indeed MySQL's BLOB types also map to that)
The FLOAT type in MySQL should be equivalent to REAL in postgres (and REAL in SQLite too) The DECIMAL type in MySQL is equivalent to DECIMAL in postgres, except that in postgres, the type does not impose an arbtrary limit on the precision, whereas in MySQL the maximum precision is (i believe) 70. (that is, 70 number positions) For both MySQL and Postgres, NUMERIC is an alias for the DECIMAL type.
这篇关于比较MySQL，PostgreSQL和SQLite中的数据库列类型？ （交叉映射）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
