SQLite3 Integer Max Value

问题:

1. what is the maximum value of data type INTEGER in sqlite3 ?
2. How do you store ip address in database ?
3. What is attached ?
4. How to create table which belongs to a specific database using sql ddl?
5. What is this error about ?

error while the list of system catalogue : no such table: temp.sqlite_master Unable to execute statement

1. Does sqlite3 text data type supoports unicode? Thanks.

回答1:

1. Look at http://www.sqlite.org/datatype3.html Maximum is 2^63-1 = 9223372036854775807
2. I would think you should use a varchar
3. http://www.sqlite.org/lang_attach.html
4. http://www.sqlite.org/lang_createtable.html
5. might be of help SQLite 'no such table' error

in general check out the sqlite documentation

回答2:

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer).

from http://www.sqlite.org/datatype3.html

Unless you have some other reason not to, you can store IP address using TEXT.

回答3:

Regarding the second question:

You can store IP address in DB in 2 ways:

1. As a String. This is recommended as it will support both IPv4 and IPv6 and does not require no additional hassle with IP address conversions.
2. As an integer. IP is basically 4 bytes that can all be merged into one integer value. However, do you really want that? That will give you loads of pain converting it to/from string any time it is required.

回答4:

• How do you store ip address in database ?

The easiest way is to store the string form (e.g., “127.0.0.1” or “::1”) since then you can read them manually and reparsing to an address structure (if you have to) is easy. SQLite likes strings (which use the TEXT type) and handles them efficiently.

回答5:

Does sqlite3 text data type supports unicode?

Yes and no.

Yes in that SQLite lets you store TEXT data in UTF-8 or UTF-16. (Use PRAGMA ENCODING to choose the internal format.)

No in that the built-in LOWER and UPPER functions only affect ASCII characters. But you can redefine functions and collations to add this support. There's an ICU extension to SQLite that does this.