CODEWITHSUNDEEP
phpmysqlsqlite
user4166144
user4166144

Reputation: 251

MySQL to SQLite migration. Do I need to update the data types?

I migrated our application from MySQL to SQLite using mysql2sqlite.sh.

I updated our PDO connection string and it continues to work without issue for some weeks but whilst browsing the database using DB Browser for SQLite I came across something odd; it appears the script didn't update the data types to be SQLite compatible, but it continues to work. How is this?

Table

CREATE TABLE `events` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `Forename`  varchar(255) NOT NULL,
    `Surname`   varchar(255) NOT NULL,
    `Site`  TEXT,
    `Badge` varchar(255) NOT NULL,
    `Start` datetime NOT NULL,
    `End`   datetime NOT NULL,
    `Created`   timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

How does it continue to work without issue whilst using MySQL data types?

According to SQLite documentation most of these data types don't exist in SQLite but it continues to work.

Should I alter the data types to TEXT or something else supported by SQLite?

Update

According to SQLite documentation, these are data types:

INTEGER
TEXT
BLOB
REAL
NUMERIC

but it continues to work without issue using:

varchar(255)
datetime
timestamp

How is this?

Upvotes: 2

Views: 135

Answers (2)

Yusuf Celik
Yusuf Celik

Reputation: 21

The reason why it is working is because SQLite knows which native datatypes Mysql data types are similar to. This is the principle of 'Affinity Names'.

For example, all INT, TINYINT, BIGINT of Mysql are all encapsulated by the SQLite INTEGER data type.

For further reading I suggest you read the docs, specifically what is belonging to the heading Affinity name examples

Upvotes: 0

Jay Blanchard
Jay Blanchard

Reputation: 34416

From the docs -

SQLite essentially supports what is called "type affinity". SQLite performs a conversion of things like varchar to text for storage purposes.

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

SQLite breaks up the affinity into groups (TEXT, NUMERIC, INTEGER, REAL, NONE) and each group honors standard SQL data types for conversion in those columns.

You do not have to alter data types during your conversion as SQLite will recognize and convert as appropriate to its affinity types.

Upvotes: 2

Related Questions