<p>SQLite不强制强列类型。相反,SQLite使用了一种称为<em>类型相似性</em>的东西:有一种<em>首选的</em>存储类型,它是从列声明和一些内部规则派生的,但是您可以很好地以数字形式存储文本,反之亦然。你知道吗</p>
<p><a href="http://www.sqlite.org/datatype3.html#affname" rel="nofollow">official documentation</a>清楚地解释了确定用于存储数据的类型的规则:</p>
<blockquote>
<p>The affinity of a column is determined by the declared type of the
column, according to the following rules in the order shown:</p>
<ol>
<li><p>If the declared type contains the string "INT" then it is assigned INTEGER affinity.</p></li>
<li><p>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.</p></li>
<li><p>If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE.</p></li>
<li><p>If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.</p></li>
<li><p>Otherwise, the affinity is NUMERIC.</p></li>
</ol>
<p>Note that the order of the rules for determining column affinity is
important. A column whose declared type is "CHARINT" will match both
rules 1 and 2 but the first rule takes precedence and so the column
affinity will be INTEGER.</p>
</blockquote>
<p>由于您的列被声明为“string”,无法派生到某个特定的类型关联,SQLite将尽可能从规则5中将数据存储为数字。因此,如果可以将值转换为数值,则删除前导零<em>。你知道吗</p>
<p>但是请注意,您可以存储文本(例如:“t00000084”),而不会出现任何问题,因为SQLite将无法将其转换为数字(首选类型关联),而是将其存储为文本。你知道吗</p>
<hr/>
<p>考虑到我们的特殊需要,您应该使用<em>text</em>affinity声明您的列。即使用包含字符串<code>CHAR</code>、<code>CLOB</code>或<code>TEXT</code>类型的类型(规则2)。你知道吗</p>