sqlite3 binary data type

Question

I am looking at migrating a small sqlite3 db to mysql. I know mysql but new to sqlite3 so have been reading about it online. I used pragma table_info(<table_name>) to get info about the table structure.

From the output I could understand columns with data type TEXT, INTEGER but i do not understand datatype BINARY(32). From sqlite3 documentation on the net there is a BINARY collation, but there is no BINARY datatype. So I just want to understand this this BINARY(32) datatype. Thanks.

Answer 1

SQLite is unusual in datatypes (column types). You can store any type of data in any type of columns with the exception of the rowid column or an alias of the rowid column.

• see Rowid Tables
• rowid is similar to MySQL AUTO INCREMENT BUT beware of differences
  • In the example below see how the rowid starts from -100, then -99 .....
  • AUTOINCREMENT on SQLite is only a constraint as such that enforces that a new id is higher than any existing in the table.

So BINARY, BINARY(32), (rumplestistkin even) are valid for the datatype when defining a column.

However, a column will be given a column affinity and governed by the rules :-

1. If the column type contains INT the the affinity is INTEGER.
2. If the column type contains CHAR, CLOB or TEXT, then it's affinity is TEXT.
3. If the column type contains BLOB then it's affinity is BLOB.
4. If the column type contains REAL FLOA or DOUB then it's affinity is REAL.
5. Otherwise the affinity is NUMERIC.

As such BINARY(32) is NUMERIC affinity. However, the column type is of little consequence in regards to storing data. The affinity can affect retrieval a little.

In regard to converting the rules mentioned above could be utilised you could also perhaps find the typeof function of use (example of it's use is in the example along with the results). However, neither will necessarily, indicate how the data is subsequently used which could well be a factor that needs consideration.

• SQLite's flexibility with column types aids in converting from other relational databases BUT can be a bit of a hindrance when converting from SQLite.

• Note this answer is by no means intended to be comprehensive explanation of the conversion from SQLite to MysQL.

See Datatypes in SQLite

Here's an example that shows that any type can be stored in any column (thus any row/col combination can store different types) :-

DROP TABLE IF EXISTS example;
CREATE TABLE IF NOT EXISTS example (
    rowid_alias_must_be_unique_integer INTEGER PRIMARY KEY, -- INTEGER PRIMARY KEY makes the column an alias of the rowid
    col_text TEXT,
    col_integer INTEGER,
    col_real REAL,
    col_BLOB BLOB,
    col_anyother this_is_a_stupid_column_type
);

INSERT INTO example VALUES (-100,'MY TEXT', 340000,34.5678,x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',100);
INSERT INTO example (col_text,col_integer,col_real,col_blob,col_anyother) VALUES

    ('MY TEXT','MY TEXT','MY TEXT','MY TEXT','MY TEXT'),
    (100,100,100,100,100),
    (34.5678,34.5678,34.5678,34.5678,34.5678),
    (x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff')
;

SELECT 
    *,
    rowid, 
    typeof(rowid_alias_must_be_unique_integer),
    typeof(col_text),
    typeof(col_integer),
    typeof(col_real),
    typeof(col_blob),
    typeof(col_anyother)
FROM example
;
DROP TABLE IF EXISTS example;

Running the above results in (Note different SQLtools handle blobs in different ways, Navicat was used to run the above) :-

• note that the typeof function returns the storage type as opposed to the affinity. However, the affinity can affect the storage type.
  • e.g. if the affinity is text then with the exception of a blob the value is stored as text. (see 2. in Datatype in SQLite above).