SQLite的动态数据类型机制

最新推荐文章于 2024-07-26 16:36:16 发布

azurelaker

最新推荐文章于 2024-07-26 16:36:16 发布

阅读量952

点赞数

分类专栏： SQLite 文章标签： SQLite 数据库

本文链接：https://blog.csdn.net/azurelaker/article/details/83152426

版权

SQLite 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

SQLite数据库的数据类型是动态的, 这区别于所有其他SQL数据库. 有些人认为这是SQLite的一个bug, 但是SQLite的设计者认为这是一个特性,而非bug, 这个特性可以更好的适应那些动态类型编程语言(如python)使用数据库接口.

SQLite在创建表格时,为列指定的数据类型可以和行插入操作(insert)中的实际数据类型不同, 其实就是对于任何列可以存储任何数据类型.下面通过一个例子说明SQLite的动态数据类型特性.

static void exec_callback(void *NotUsed, int argc, char **argv, char **azColName) {
	int i;
	for(i = 0; i < argc; i++) {
		printf("exec_callback. %s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
	}
}

static void dynamic_datatype_test1() {
	sqlite3 *db = 0;
	char *zErrMsg = 0;

	// Open database.
	int rc = sqlite3_open("mysqlite.db", &db);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Create table.
	char *create_table_cmd = "CREATE TABLE tl (info TEXT, line INT);";
	rc = sqlite3_exec(db, create_table_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Insert.
	char *insert_cmd = "INSERT INTO tl  VALUES ('column 1 is text data type.', 'column 2 is integer data type, but insert text now.');";
	rc = sqlite3_exec(db, insert_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Query.
	char *query_cmd = "select * from tl;";
	rc = sqlite3_exec(db, query_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	out:
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
	}
	sqlite3_free(zErrMsg);
	sqlite3_close(db);
}

在数据库中创建表时, 第一列是TEXT类型, 第二列是INT类型, 但是我们在往表里面插入行数据时, 可以对INT类型的列插入TEXT类型的数据, 而不会发生错误. 同时, 查询数据库的时候, 可以从表里面获得插入时的原始数据.SQLite的sqlite3_exec函数是个wrapper接口,其相当于调用了prepare, step等其他接口, 而和sqlite3_exec配合使用的回调函数,只有在查询操作时(即有结果时),才被回调执行, 从该函数的原型可以看出, 其返回的行数据可以看成二进制,因此根据使用场景,可能需要自己再进一步处理该回调函数返回的数据,比如转换成INT类型等.

下面的例子将不再使用sqlite3_exec接口函数, 而直接使用prepare, step等接口函数,并且使用指定的接口函数返回特定类型的列值.

static int select_dynamic_data(sqlite3 *db) {
	int rc = -1;
	sqlite3_stmt *pStmt = NULL;
	rc = sqlite3_prepare_v2(db, "select * from tl2;", -1, &pStmt, 0);
	if (rc != SQLITE_OK) {
	  return rc;
	}

	while (1) {
		rc = sqlite3_step(pStmt);
		if (rc == SQLITE_DONE) {
			break;
		}
		assert(rc == SQLITE_ROW);

		const unsigned char *column1_value = sqlite3_column_text(pStmt, 0);
		const unsigned char *column2_value_text = sqlite3_column_text(pStmt, 1);
		int column2_value_int = sqlite3_column_int(pStmt, 1);
		printf("column value %s %s %d\n", column1_value, column2_value_text, column2_value_int);
	}

	sqlite3_finalize(pStmt);
	return SQLITE_OK;
}

static void dynamic_datatype_test2() {
	sqlite3 *db = 0;
	char *zErrMsg = 0;

	// Open database.
	int rc = sqlite3_open("mysqlite.db", &db);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Create table.
	char *create_table_cmd = "CREATE TABLE tl2 (info TEXT, line INT);";
	rc = sqlite3_exec(db, create_table_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Insert.
	char *insert_cmd = "INSERT INTO tl2  VALUES ('column 1 is text data type.', '23. insert text actually.');";
	rc = sqlite3_exec(db, insert_cmd, exec_callback, 0, &zErrMsg);
	if(rc != SQLITE_OK) {
		goto out;
	}

	// Query.
	rc = select_dynamic_data(db);

	out:
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
	}
	sqlite3_free(zErrMsg);
	sqlite3_close(db);
}

与之前的代码片段类似, 我们为INT类型的列插入了TEXT类型的数据.当查询的时候, 既可以用sqlite3_column_text接口函数返回插入的原始数据, 也可以用sqlite3_column_int返回INT类型数据, 返回INT时,其实是把原始数据进行了转换,该函数实际的执行函数为(vdbemem.c):

/*
** Return some kind of integer value which is the best we can do
** at representing the value that *pMem describes as an integer.
** If pMem is an integer, then the value is exact.  If pMem is
** a floating-point then the value returned is the integer part.
** If pMem is a string or blob, then we make an attempt to convert
** it into a integer and return that.  If pMem is NULL, return 0.
**
** If pMem is a string, its encoding might be changed.
*/
i64 sqlite3VdbeIntValue(Mem *pMem){
  int flags = pMem->flags;
  if( flags & MEM_Int ){
    return pMem->u.i;
  }else if( flags & MEM_Real ){
    return (i64)pMem->r;
  }else if( flags & (MEM_Str|MEM_Blob) ){
    i64 value;
    pMem->flags |= MEM_Str;
    if( sqlite3VdbeChangeEncoding(pMem, SQLITE_UTF8)
       || sqlite3VdbeMemNulTerminate(pMem) ){
      return 0;
    }
    assert( pMem->z );
    sqlite3Atoi64(pMem->z, &value);
    return value;
  }else{
    return 0;
  }
}

SQLite操作的SQL语法中的值由内部的Mem数据结构表示,其可以是SQLite支持的任何类型, 该结构的定义为(vdbeint.h):

/*
** Internally, the vdbe manipulates nearly all SQL values as Mem
** structures. Each Mem struct may cache multiple representations (string,
** integer etc.) of the same value.  A value (and therefore Mem structure)
** has the following properties:
**
** Each value has a manifest type. The manifest type of the value stored
** in a Mem struct is returned by the MemType(Mem*) macro. The type is
** one of SQLITE_NULL, SQLITE_INTEGER, SQLITE_REAL, SQLITE_TEXT or
** SQLITE_BLOB.
*/
struct Mem {
  union {
    i64 i;              /* Integer value. Or FuncDef* when flags==MEM_Agg */
    FuncDef *pDef;      /* Used only when flags==MEM_Agg */
  } u;
  double r;           /* Real value */
  char *z;            /* String or BLOB value */
  int n;              /* Number of characters in string value, including '\0' */
  u16 flags;          /* Some combination of MEM_Null, MEM_Str, MEM_Dyn, etc. */
  u8  type;           /* One of SQLITE_NULL, SQLITE_TEXT, SQLITE_INTEGER, etc */
  u8  enc;            /* SQLITE_UTF8, SQLITE_UTF16BE, SQLITE_UTF16LE */
  void (*xDel)(void *);  /* If not null, call this function to delete Mem.z */
  char zShort[NBFS];  /* Space for short strings */
};

该结构有一个flags字段记录其表现类型,即表创建时指定的列类型, 如果sqlite3_column_xxx接口函数指定的类型和Mem中的flags的类型不一致, 则需要对原始数据做某种转换处理.

SQLite在把SQL值存储到磁盘B-tree上时, 为了支持动态数据类型,其实是使用了变长的记录结构, 因此, 如果为SQLite的某个列定义了VARCHAR(100), 其实并不会分配这个固定长度的空间,其实际分配的空间取决于实际值占用的空间.