IOS SQLite3 usage and tips

The client for iOS applications generally stores data in SQLite3. The following describes the data types supported by iOS SQLite3 and their relationships. Most databases use static strict type systems. The column type is specified when creating a table. SQLite uses a dynamic type system. The column type is determined by the value.

Data Types of iOS SQLite3

 
 

  
  
NULL:NULLvalue 
 
 

Integer: The value belongs to the signedinteger type. The value can be 1, 2, 3, 4, 6, or 8 bytes.

• REAL: floating point type
• TEXT: character type stored in UTF-8, UTF-16BEorUTF-16LE Encoding
• BLOB: binary data

Other data types

The Integerprimarykey column is an exception ??

• Boolean Type: SQLite does not define the boolean type, but stores boolean values by Integer, 0 (false), 1 (true)

Dateandtime type

The date and time types are not defined in SQLite. the date and time can be stored in TEXT, REAL, or orINTEGER.

TEXT: stored as a string ("YYYY-MM-DDHH: MM: SS. SSS ").

 
 

  
  
REAL:asJuliandaynumbers,thenumberofdayssincenooninGreenwichonNovember24,4714B.C.accordingtotheprolepticGregoriancalendar.   
  
  
   
  
  
INTEGER:asUnixTime,thenumberofsecondssince1970-01-0100:00:00UTC. 
 
 

SQLiteTypeAffinity (type detection)

The type of the value used for automatic detection. The following lists how Affinity determines the type rules.

(1) If the type declaration contains int, use INTEGERaffinity.

(2) If the type declaration contains "CHAR", "CLOB", or "TEXT", use Textaffinity

(3) If the type declaration contains BLOB or the type is not specified, use affinityNONE

(4) If the type declaration contains "REAL", "FLOA", or "DOUB", use REALaffinity

(5) otherwise use Numericaffinity

Type comparison NULL

Memcmp function prototype

 
 

  
  
intmemcmp(constvoid*ptr1,constvoid*ptr2,size_tnum); 
 
 

Compares the two pointers to the first num bytes in the memory.

Type conversion before comparison

L (INTEGER, REALorNUMERIC) and (TEXTorNONE), the TEXT and NONE will be converted to NUMERIC

When lTEXT and NONE are compared, NONE is converted to TEXT

Compare other cases directly.

Summary: The content of the iOS SQLite3 type system has been introduced. I hope you can learn the operation content of iOS SQLite to help you.