SQLite不能正确存储小数 [英] SQLite not storing decimals correctly

查看：207
本文介绍了SQLite不能正确存储小数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个带tbl_invent表的sqlite DB，在窗体加载时，它用表中的内容填充datagridview。问题是我的字段名称cost和sell_price具有小数点，并且在加载表格时仅显示数字而不是小数。

I have a sqlite DB with a table called tbl_invent, on form load it fills the datagridview with what is in the table. The problem is I have field names cost and sell_price which have decimals, and when the form loads it only shows the number not the decimal.

sample：

表= 1.75，DGV = 1.00

Table=1.75, DGV=1.00

   Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    connect()
    Dim da As New SQLiteDataAdapter("select * from tbl_Invent", connection)
    Dim ds As New DataSet
    da.Fill(ds, "tbl_Invent")
    DataGridView1.DataSource = ds
    DataGridView1.DataMember = "tbl_Invent"
    DataGridView1.Columns(6).ValueType = GetType(Single)
    DataGridView1.Columns(6).DefaultCellStyle.Format = "N2"
    DataGridView1.Columns(7).ValueType = GetType(Single)
    DataGridView1.Columns(7).DefaultCellStyle.Format = "N2"

    connection.Close()
    da.Dispose()
End Sub

我已经检查了字段类型，它是正确的整数，我也尝试了 GetType（Single）和 GetType（Decimal），但仍然相同。谁能指出我正确的方向？谢谢。

i already check the field type it's correct "Integer", i also tried the "GetType(Single)" and "GetType(Decimal)" but still the same. any one could point me in the right direction? thank you.

来自评论：

没有SQLite中的其他类型。在SQLite中也只有 Text， Integer， Real和 Blob，它表示整数可以有小数。

there is no other type in SQLite. there is only "Text", "Integer", "Real" and "Blob" also in SQLite it says integer can have decimals.

推荐答案

您没有指出正在使用的数据库提供程序，但是标准提供程序（来自SQLite开发人员）将看到 Integer 并将数据映射到NET Int32 类型，不允许使用小数。 Real 可以保存小数，而 Decimal 即可。

You did not indicate which DB provider you were using, but the standard provider (from the SQLite devs) will see Integer and map the data to the NET Int32 type which doesn't allow decimals. Real would save fractionals as would Decimal.

SQLite中没有其他类型。只有 Text， Integer， Real和 Blob

是的，但这适用于SQLite数据库，不是数据库提供者。标准的DB Provider巧妙地编写为能够将4种基本类型转换为各种NET类型，从而使实际的存储类型/格式成为实现细节。

That's true but it applies to the SQLite DB, not the DB Provider. The standard DB Provider is cleverly written to be able to convert the 4 basic types to a variety of NET types such that the actual storage type/format becomes an implementation detail.

提供者代码包括许多步骤，查找表，子系统，字典和执行转换的方法。甚至还有一种定义自定义类型名称的方法。以下是工作原理的概括性说明。

The provider code includes a number of steps, look-up tables, sub systems, dictionaries and methods to perform conversions. There is even a way to define custom type names. The following is a generalized explanation of the workings.

字节，SByte

INT8，INTEGER8，TINYSINT（字节）
UINT8，UNSIGNEDINTEGER8，TINYINT（字节）

Byte, SByte
INT8, INTEGER8, TINYSINT (SByte) UINT8, UNSIGNEDINTEGER8, TINYINT (Byte)

积分（短，长，有符号，无符号等）

BIGINT，BIGUINT，COUNTER，IDENTITY，INT，INT16，INT32，INT64，INTEGER ，INTEGER16，INTEGER32，INTEGER64，LONG，SMALLINT，SMALLUINT，UINT，UINT16，UINT32，UINT64，ULONG，UNSIGNEDINTEGER，UNSIGNEDINTEGER16，UNSIGNEDINTEGER32，UNSIGNEDINTEGER64

Integral (short, long, signed, unsigned etc)
BIGINT, BIGUINT, COUNTER, IDENTITY, INT, INT16, INT32, INT64, INTEGER, INTEGER16, INTEGER32, INTEGER64, LONG, SMALLINT, SMALLUINT, UINT, UINT16, UINT32, UINT64, ULONG, UNSIGNEDINTEGER, UNSIGNEDINTEGER16, UNSIGNEDINTEGER32, UNSIGNEDINTEGER64

Boolean

位，布尔值，布尔值，逻辑值，是否

Boolean
BIT, BOOL, BOOLEAN, LOGICAL, YESNO

文本/字符串

CHAR，CLOB，LONGCHAR，LONGTEXT，LONGVARCHAR，MEMO，NCHAR，NOTE，NTEXT，NVARCHAR，STRING，TEXT，VARCHAR，VARCHAR2

Text/String
CHAR, CLOB, LONGCHAR, LONGTEXT, LONGVARCHAR, MEMO, NCHAR, NOTE, NTEXT, NVARCHAR, STRING, TEXT, VARCHAR, VARCHAR2

数值 >

双重，浮动，真实；单（单）

Numeric
DOUBLE, FLOAT, REAL; SINGLE (Single)

小数

货币，十进制，货币，数字，数字

Decimal
CURRENCY, DECIMAL, MONEY, NUMBER, NUMERIC

BLOB

二进制，BLOB，常规，图像，OLEOBJECT，RAW，VARBINARY

BLOB
BINARY, BLOB, GENERAL, IMAGE, OLEOBJECT, RAW, VARBINARY

日期/时间

日期，DATETIME，SMALLDATE，时间，时间戳

Date/Time
DATE, DATETIME, SMALLDATE, TIME, TIMESTAMP

GUID

GUID，唯一性

GUID
GUID, UNIQUEIDENTIFIER

来源： SQLiteConvert中的 SQLiteDbTypeMap 。 cs （版本1.0.103； 2016年9月）。

Source: SQLiteDbTypeMap in SQLiteConvert.cs (version 1.0.103; September, 2016).

从本质上讲，DBProvider 以适当的SQLite类型存储数据，但是当读取时将其备份使用表定义中使用的类型将数据转换回NET类型。 SQLite提供程序包括一个大型的 SQLiteConvert 类，可以为您完成所有转换。

In essence, the DBProvider stores the data in the appropriate SQLite type, but when it is read back it uses the type you used in the table definition to convert the data back to a NET type. The SQLite provider includes a large SQLiteConvert class to do all the conversions for you.

尽管对于SQLite奉献者来说这似乎是常识，但我无法在野外找到记录。大多数站点只是重新格式化SQLite网站内容。它可能记录在帮助文件中，但是我的主题没有内容。给定该列表，很容易意外使用有效名称并发现其有效。

I cannot find this documented in the wild, though it seems to be common knowledge to SQLite devotees. Most sites just reformat the SQLite site content. It might be documented in the help file, but mine has topics with no content. Given the list, it is easy to accidentally use a valid name and discover it works.

该列表包含了其他数据库使用的最常见的符号，以及一些NET类型。 。例如，可以将 Boolean 定义为 BIT，BOOL，BOOLEAN，LOGICAL或YESNO 。因此，此表定义是合法的且具有完整功能：

The list incorporates the most common notations used by other DBs, plus a few NET types. For example, Boolean can be defined as BIT, BOOL, BOOLEAN, LOGICAL or YESNO. As a result, this table definition is legal and fully functional:

CREATE TABLE LiteColTypes (
    Id        INTEGER     PRIMARY KEY AUTOINCREMENT,
    Name      TEXT,
    ItemDate  DATETIME,
    Char3     CHAR (3),
    UINT32    UINT32,
    Value     INT16,
    VarChar5  VARCHAR (5),
    GCode     GUID,
    Price     DECIMAL,
    ItemImg   IMAGE,
    Active    BOOL,
    NotActive YESNO
);

有几件事要注意，一些有用的 DateTime 选项。

There are a few things to be aware of and some useful DateTime options.

列表来自以下代码：

/// <summary>
/// Builds and returns a map containing the database column types
/// recognized by this provider.
/// </summary>
/// <returns>
/// A map containing the database column types recognized by this
/// provider.
/// </returns>
private static SQLiteDbTypeMap GetSQLiteDbTypeMap()
{
return new SQLiteDbTypeMap(new SQLiteDbTypeMapping[] {
    new SQLiteDbTypeMapping("BIGINT", DbType.Int64, false),
    new SQLiteDbTypeMapping("BINARY", DbType.Binary, false),
    new SQLiteDbTypeMapping("BIT", DbType.Boolean, true),
    new SQLiteDbTypeMapping("BLOB", DbType.Binary, true),
    new SQLiteDbTypeMapping("BOOL", DbType.Boolean, false),
    new SQLiteDbTypeMapping("BOOLEAN", DbType.Boolean, false),
    ...
    new SQLiteDbTypeMapping("GUID", DbType.Guid, false),
    new SQLiteDbTypeMapping("IMAGE", DbType.Binary, false)
    ... (many more)

保留XML注释是因为它具有启发性和权威性：

The XML comment was retained because it is illuminating and authoritative:


建立并返回包含数据库该提供者认可的列类型的地图。
（强调我的）。

Builds and returns a map containing the database column types recognized by this provider. (emphasis mine).

DbType 对于该过程至关重要。

The DbType is crucial to the process.

上面的 SQLiteDbTypeMap 将那些它识别为 DbType 的许多许多列名称，这些列名称用于确定要返回的NET数据类型。该列表足够全面，可以为您转换除1种或2种类型之外的所有类型。

The above SQLiteDbTypeMap associates those many, many column names it recognizes to a DbType which is used to determine the NET data type to return. The list is comprehensive enough that it can convert all but 1 or 2 types for you.

例如，请注意 GUID 和 IMAG *都作为 BLOB 存储，但 GUID 类型名称与另一个 DbType 关联，这使得那个 BLOB的返回不同于 IMAGE BLOB。

For example, note that GUID and IMAG* are both stored as BLOB, but the GUID type name is associated with a different DbType which allows that BLOB to be returned differently than an IMAGE BLOB.

还可以通过连接对象指定类型。空格和作用域不允许解释，但是虽然有点乏味，但它允许您为自定义类型名称提供数据类型。

You can also specify types via the connection object. Space and scope does not permit an explanation, but while a bit tedious, it allows you to provide the data type for custom type names.

存储数据时，您不必担心它如何应该存储。数据库提供者将使用传递的 DbType 查找要使用的SQLite类型（相似性）。如果使用 AddWithValue 或（过时的） Add（object，object）重载，DBProvider会猜测类型。猜测非常好，但是不要这么做。

When storing data, you need not fret about how it should be stored. The DB Provider will use the DbType passed to look up SQLite type to use (Affinity"). If you use AddWithValue or the (obsolete) Add(object, object) overload, the DBProvider will guess at the type. It's pretty good at guessing, but dont do that.

因此，不需要此转换：

cmd.Parameters.Add("@g", DbType.Binary).Value = myGuid.ToByteArray();

使用与任何其他数据库相同的代码：

Use the same sort of code you would/should with any other database:

' // add trailing semicolons for c#
cmd.Parameters.Add("@n", DbType.String).Value = "Ziggy"
cmd.Parameters.Add("@dt", DbType.DateTime).Value = DateTime.Now 
cmd.Parameters.Add("@c3", DbType.StringFixedLength, 3).Value = "XYZ123" '// see notes
cmd.Parameters.Add("@u", DbType.UInt16).Value = 3
cmd.Parameters.Add("@g", DbType.Guid).Value = myGuid
    cmd.Parameters.Add("@p", DbType.Decimal).Value = 3.14D

'// 'ToByteArray()' is an extension method to convert
cmd.Parameters.Add("@img", DbType.Binary).Value = myImg.ToByteArray()
cmd.Parameters.Add("@act", DbType.Boolean).Value = True

注意：


  • 使用 DbType 描述传递的数据，而不是保存您认为的 （例如 DbType.Guid ，而不是的 Binary ）。提供程序将执行大多数转换。

  • 没有 DbType.Image ，因此字节数组转换是

  • 为 Char（）/ VarChar（）字段指定大小不会限制保存的字符数。这似乎是一个错误，因为保存的字符数超出定义的字符数会阻止该行的加载。

  • A UInt16 的工作方式相反：试图传递超出范围的值（例如，对于UInt16为-5），将导致溢出异常。但是对于已存储的值，它将返回 65531 。

  • 大小/精度参数，例如 Decimal（ 9,2）似乎无关紧要。内部表提供固定的精度和大小。

  • 对于日期，请传递日期并指示 DbType.DateTime 。永远不需要传递特定格式的字符串。提供者知道事情。 （请参阅下面的 DateTime选项。）

  • 仅保存日期，仅传递日期： .Value = DateTime.Now.Date 。

  • Use the DbType which describes the data passed, not how you think it should be saved ( e.g. DbType.Guid, not Binary for a Guid). The provider will perform most conversions.
  • There is no DbType.Image so a byte array conversion is needed.
  • Specifying a size for a Char()/VarChar() field does not limit the number of characters saved. This seems like a bug because saving more characters than defined can prevent the row from loading.
  • A UInt16 works in reverse: trying to pass an out of range value, such as -5 for a UInt16, will result in an Overflow Exception. But it will return 65531 for such a value already stored.
  • Size/precision parameters such as Decimal(9,2) for a column doesn't seem to matter. An internal table provides fixed precision and sizes.
  • For dates, pass dates and indicate DbType.DateTime. There is no need to pass strings of a particular format ever. The provider Knows Things. (See DateTime Options below.)
  • To save the Date only, pass only the date: .Value = DateTime.Now.Date.

两个不同的查找表用于保存和读取数据，它们的共同点是 DbType 这就是为什么它很重要。使用正确的数据可确保数据可以往返。避免使用 AddWithValue 。

Two Different look-up tables are used for saving versus reading data, the one thing they have in common is the DbType which is why it is important. Using the correct one assures that data can make the round trip. Avoid using AddWithValue.


从UI浏览器中查看数据

加载数据不需要任何特殊操作：

Nothing special is required to load data:

 // Dim SQL = "SELECT * FROM LiteColTypes"   ' for VB
 string SQL = "SELECT * FROM LiteColTypes";      
 ...
 dbCon.Open();
 Dim dt As New DataTable();
 dt.Load(cmd.ExecuteReader());
 dgv.DataSource = dt;



DataGridView中的相同数据

DGV可以正确识别并显示GUID，图像和布尔值列。每个 DataColumn 的数据类型均符合预期：

A DGV correctly identifies and displays the GUID, Image and Boolean columns. The data types of each DataColumn are as expected:


       Name --->    System.String (maxLen = 2147483647)  
   ItemDate --->  System.DateTime  
      Char3 --->    System.String (maxLen = 3)  
     UINT16 --->    System.UInt16  
   VarChar5 --->    System.String (maxLen = 5)  
      GCode --->      System.Guid  
      Price --->   System.Decimal  
    ItemImg --->    System.Byte[]  
     Active --->   System.Boolean  
  NotActive --->   System.Boolean


请注意 Guid 和 Image 项目都存储为 BLOB ，但返回的方式有所不同。使用了 Active （ BOOL ）和 NotActive （ YESNO ）类型名称不同，但返回相同的数据类型。一切正常。

Note that Guid and Image items were both stored as BLOB but are returned differently. Active (BOOL) and NotActive (YESNO) used different type names but return the same data type. Everything works as desired.

时间作为列类型名称不能按预期方式工作。它不会解析 DateTime.Now.TimeofDay （ Timespan ）。该表将TIME映射到 DbType.DateTime 。

TIME as a column type name doesn't quite work as expected. It does not parse DateTime.Now.TimeofDay (Timespan) to it. The table maps TIME to DbType.DateTime.

请勿使用 DbType.DateTime2 或 .DateTimeOffset 。这些在转换器查找中丢失，因此数据以无效格式（版本1.0.103.0）作为文本存储。

Do not use DbType.DateTime2 or .DateTimeOffset. These are missing in converter look-ups so data is stored as Text in an invalid format (version 1.0.103.0).

SQLite NET Provider不仅支持一种日期格式。当另存为UTC时，数据包括一个指示符。但是，无论保存为本地还是UTC，种类总是返回为未指定。解决此问题的方法之一是在连接字符串中添加 datetimekind ：

The SQLite NET Provider does not support just one date format. When saving as UTC, the data includes an indicator. But, whether saved as Local or UTC, the Kind always returns as Unspecified. Part of the remedy for this is to add datetimekind to your connection string:

`...;datetimekind=Utc;`
`...;datetimekind=Local;`

这将为所有 DateTime 返回的值设置种类，但<

This will set the Kind for all DateTime values returned but without converting the value.

此的解决方法是使用（相对）新的 BindDateTimeWithKind 连接标志。保存 时，它将转换日期以匹配连接的 DateTimeKind ：

The remedy for this is to use the (relatively) new BindDateTimeWithKind connection flag. This will convert dates to match the DateTimeKind of the connection when saved:

Private LiteConnStr = "Data Source='C:\Temp\demo.db';Version=3;DateTimeKind=Utc;"
...
Dim dt As New DateTime(2011, 2, 11, 11, 22, 33, 444, DateTimeKind.Local)

Using dbCon = New SQLiteConnection(LiteConnStr)
    dbCon.Flags = SQLiteConnectionFlags.Default Or 
                  SQLiteConnectionFlags.BindDateTimeWithKind
    ...
    cmd.Parameters.Add("@dt", DbType.DateTime).Value = dt

    ' == 2011-02-11 17:22:33.444Z   note the hour

尽管已传递本地日期，但 BindDateTimeWithKind 会将其保存为UTC以匹配连接。由于 DateTimeKind = Utc，返回UTC日期；连接设置。

Though a local date was passed, BindDateTimeWithKind results in it being saved as UTC to match the connection. A UTC date is returned due to the "DateTimeKind=Utc;" connection setting.

请注意， DateTimeKind 在日期 read 上有效，而 BindDateTimeWithKind 在保存日期时起作用。个别地看，它们似乎会使情况更糟。 一起，整个数据库将成为基于UTC（或本地）的数据库，日期统一保存并读取为相同的种类 －您无需执行任何操作。

Note that DateTimeKind works on dates read while BindDateTimeWithKind acts when saving dates. Individually they can seem to make things worse; together the entire database becomes UTC based (or Local) with dates uniformly saved and read as the same Kind -- you need not do anything.

ConnectionFlags 手动操作可能很麻烦，可以在连接字符串中指定它们：

ConnectionFlags can be tedious to work with manually, to specify them in the connection string:

connx = "...;datetimekind=Utc;flags='Default, BindDateTimeWithKind';"

限制/问题

统一的 Kind 处理与 DbDataReader 至少与Dapper一起使用时效果很好。但是，当使用 DataTable 时，日期的种类仍未指定。显然，这是由于 DataColumn 中的 DateTimeMode 属性所致，并且可能是Microsoft的一项设计决定，即不假定日期中的所有日期。列将始终是相同的种类。

The uniform Kind treatment works well with a DbDataReader and at least with Dapper. But when using a DataTable, the Kind for dates remains Unspecified. This is apparently due to the DateTimeMode property in DataColumn and probably a design decision by Microsoft not to assume all dates in a column will always be the same Kind. This manifests in other DBs as well.

使用UTC或本地连接时，提供程序将不指定未指定状态（这也适用于查询中的日期）。因此，不应有任何不希望的额外转换：在更新中 不会再次将 DataTable 中的UTC日期读取和伪装为未指定。 。

When using a UTC or Local connection, the provider leaves Unspecified alone (this applies to dates in queries as well). So there should not be any undesired extra conversions: a UTC date read and 'disguised' as Unspecified in a DataTable isn't converted again in updates.

与常规智慧日期相反，并非总是且仅保存为TEXT；为了节省一点空间，您可以保存刻度值。由于这些不能具有时区指示器，因此与种类相关的选项可能非常有用。要启用Ticks，请使用 DateTimeFormat 连接字符串选项：

Contrary to 'conventional wisdom' dates are not always and only saved as TEXT; to save a little space, you can save the tick value. Since these cannot have a Time Zone indicator, the Kind related options can be very useful. To enable Ticks, use the DateTimeFormat connection string option:

Private LiteConnStr = "...;datetimekind=Utc;DateTimeFormat=Ticks;..."
'e.g: 634939900800000000

其他 DateTimeFormat 选项包括 CurrentCulture ， ISO8601 （默认）， JulianDay 和 UnixEpoch 。无需更改列类型名称即可使用这些格式之一。现在仍然是日期，SQLite Provider会根据连接标志来处理实现细节。

Other DateTimeFormat options include CurrentCulture, ISO8601 (the default), JulianDay and UnixEpoch. There is no need to change the column type name to use one of these formats. It is still a date, the SQLite Provider handles the implementation details based on the connection flags.

UI浏览器

许多SQLite UI浏览器似乎只知道四种规范类型。也许这是故意的，但这限制了它们对NET开发人员的实用性，并隐藏了NET提供程序的功能。

Many SQLite UI Browsers seem to only know about the four canonical types. Perhaps this is intentional, but this limits their usefulness for NET developers and hides the capabilities of the NET provider.

SQLiteStudio（版本：3.1.0）提供了更多功能，但它似乎并不知道完整的列表，因为缺少了一些非常有用的列表（例如GUID，IMAGE，SINGLE，整数变体）。

SQLiteStudio (version: 3.1.0) offers a few more, but it doesn't seem to be aware of the complete list, because some very useful ones are missing (e.g. GUID, IMAGE, SINGLE, integer variants).

它确实可以让您输入所需的任何类型名称，因此，Profit！

It does allow you to enter any type name you want, so Profit!


  • 。NET Provider通过支持各种类型的列名称来为SQLite添加功能，

  • 每个受支持的名称都与 DBType 相关联，该类型确定实际的返回数据类型

  • 使用保存数据时确保 DbType 正确，以确保数据往返。

  • NET Provider将为您执行大多数转换

  • DateTimeKind 和 BindDateTimeWithKind 选项允许自动统一地对日期进行时区存储

  • The NET Provider adds functionality to SQLite by supporting a wide variety of type names for columns,
  • Each supported name is associated with a DBType which determines the actual return data type
  • Using the correct DbType when saving data assures the data makes the round trip
  • The NET Provider will perform most conversions for you
  • The DateTimeKind and BindDateTimeWithKind options allow for automatic, uniform TimeZone storage of dates

首先，NET提供程序将实际存储作为实现详细信息。

Above all, the NET provider makes the actual storage an implementation detail.

这篇关于SQLite不能正确存储小数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆