将数字作为字符串插入到文本列中，SQLite静止图像将删除前导零 [英] Inserting a number as a String into a Text column and SQLite stills removes the leading zero

问题描述

我得到以下数字作为字符串：字符串numberString =079674839;

I got the following number as a string: String numberString = "079674839";

当我将此数字插入SQLite数据库时，SQLite会自动删除前导零并将字符串存储为79674839.考虑到亲和性以及列存储TEXT，SQLite不应该存储整个字符串并保持前导零吗？

When I insert this number into a SQLite DB, SQLite automatically removes the leading zero and stores the string as 79674839. Considering affinity and that the column stores TEXT, shouldn't SQLite store the whole string and keep the leading zero?

谢谢

推荐答案

仔细检查数据库架构。如 SQLite版本3中的数据类型所述，列类型名称会影响值在存储之前的处理方式。

Double-check your database schema. As documented on Datatypes in SQLite Version 3, the column type name affects how values are processed before being stored.

这是一个使用内存数据库演示的Python程序：

Here's a Python program to demonstrate, using an in-memory database:

import sqlite3

db = sqlite3.connect(':memory:')
val = "0796";
db.execute('CREATE TABLE test (i INTEGER, r REAL, t TEXT, b BLOB);')
db.execute('INSERT INTO test VALUES (?, ?, ?, ?);', (val, val, val, val))
res = db.execute('SELECT * FROM test');
print '\t'.join([x[0] for x in res.description])
for row in res.fetchall():
    print '\t'.join([repr(x) for x in row])

输出为：

i    r      t       b
796  796.0  u'0796' u'0796'

因此，看起来你的列实际上是一个整数类型。看一下模式定义（ sqlite3 database.db .schema 从命令行工作），查看文档，并确保使用映射到TEXT亲缘关系的类型名称之一。未知类型名称获得INTEGER亲和力。

So, it looks like your column is actually an integer type. Take a look at the schema definition (sqlite3 database.db .schema works from the command line), look at the documentation again, and make sure you are using one of type names that map to TEXT affinity. Unknown type names get INTEGER affinity.

在我自己的情况下，我使用'STR'，最终得到默认的INTEGER亲和力。我将其更改为TEXT，SQLite开始尊重我的前导零。

In my own case, I was using 'STR', which ends up with the default INTEGER affinity. I changed it to 'TEXT', and SQLite started respecting my leading zeros.

这篇关于将数字作为字符串插入到文本列中，SQLite静止图像将删除前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！