MySQL, PostgreSQL 및 SQLite의 데이터베이스 열 유형 비교?

MySQL, PostgreSQL 및 SQLite의 데이터베이스 열 유형 비교? (교차 매핑)

---

가장 많이 사용되는 데이터베이스 인 MySQL , PostgreSQL , SQLite에서 열 유형 을 연관시키는 방법을 찾으려고 합니다.

지금까지 제가 가지고있는 내용이 있지만 아직 완료되지 않은 것이 두렵고 누락 된 유형을 완료하는 데 도움이 될 더 많은 경험이있는 사람들이 필요합니다.

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

---

내가 다르게 할 일 목록 :

MySQL의 MEDIUMINT는 이상한 오리 (3 바이트)입니다. 나는 그것을 피할 것이지만 그렇지 않으면 INTEGER에도 매핑하십시오.

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.

참고URL : https://stackoverflow.com/questions/1942586/comparison-of-database-column-types-in-mysql-postgresql-and-sqlite-cross-map