CODEWITHSUNDEEP
sqlsqlite
sazr
sazr

Reputation: 25928

Whats the best SQLite data type for a long string

In SQLite3 I am going to be storing a very long string that looks something like this:

string linkList = "www.blah.com,www.meh.com,www.hah.com";

will definitely exceed 255 chars, will probably be over 1000 chars

Should the column linkList be a varchar, TEXT or something else to store a string that will probably be longer than 1000 chars

CREATE TABLE(uId INT PRIMARY KEY, linkList varchar);

What would be the best variable type to use for the column linkList?

Upvotes: 60

Views: 73352

Answers (4)

Gordon88
Gordon88

Reputation: 84

Size limit for TEXT (VARCHAR and others TEXT alike) are defaulted to 100 0000 000 Bytes
look for macro SQLITE_MAX_LENGTH

Can be increase (or decrease, but no gain to do that aside of code sanity maybe) using
int sqlite3_limit(sqlite3 *db, int limitId, int newLimit)
using SQLITE_LIMIT_LENGTH for limitId.

Again: aside of a need for more than ~100MB (or code sanity by trowing execption) I don't see any interest to modify default.

Upvotes: 1

Jordão
Jordão

Reputation: 56477

You should use TEXT.

Although, that's the same thing as VARCHAR:

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity

And also:

Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

"SQLite does not impose any length restrictions"

Upvotes: 80

gifpif
gifpif

Reputation: 4917

Yor have 2 choices to store a long String in SQLite

  1. TEXT type - SQLite support very long text (I don't know exact number but I tested 33000 char SQLite3)
  2. BLOB type: You can use BLOB Data type to hold long String.

Upvotes: 8

krevedko
krevedko

Reputation: 4243

Actually, all data types in SQLite is no matter, all data will be stored as strings. You can execute query CREATE TABLE(uId INT PRIMARY KEY, linkList BlahBlahString); without any error. Data type in SQLite is for sql compability only

Upvotes: 31

Related Questions