c# - Decimal 类型从 In-memory Sqlite 数据库中读取为 double 或 int

Question

我对 .Net 中的 Sqlite 没有太多经验，但我看到的行为很奇怪。假设我们有一个具有以下 project.json 的 .Net 核心应用程序:

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Microsoft.Data.Sqlite": "1.0.0",
    "Dapper": "1.50.2"
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

我们还有一个简单的类Item:

public class Item
{
    public Item() { }

    public Item(int id, string name, decimal price)
    {
        this.Id = id;
        this.Name = name;
        this.Price = price;
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

然后我创建一个内存数据库并用数据填充它(使用 Dapper):

var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();
connection.Execute("CREATE TABLE IF NOT EXISTS Items(Id INT, Name NVARCHAR(50), Price DECIMAL)");

var items = new List<Item>
{
    new Item(1, "Apple", 3m),
    new Item(2, "Banana", 1.4m)
};
connection.Execute("INSERT INTO Items(Id, Name, Price) VALUES (@Id, @Name, @Price)", items);

然后我尝试从 Items 表中读取:

var dbItems = connection.Query<Item>("SELECT Id, Name, Price FROM Items").ToList();

当我运行解决方案时，出现以下异常:

Unhandled Exception: System.InvalidOperationException: Error parsing column 2 (Price=1.4 - Double) ---> System.Invali dCastException: Unable to cast object of type 'System.Double' to type 'System.Int64'.

好的，那我尝试使用Microsoft.Data.Sqlite来获取数据:

var command = connection.CreateCommand();
command.CommandText = "SELECT Price FROM Items";
var reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader[0].GetType());
}

结果我得到:

System.Int64 // Price = 3                                                                                                         
System.Double // Price = 1.4

我尝试在真实数据库上使用十进制价格运行查询，返回的数据类型是正确的并且始终是十进制(如预期的那样)。
应该往哪个方向深挖？我的内存数据库有问题吗？如何使其与小数保持一致？

Answer 1

在 SQLite 中，如果您创建一个具有数字/小数列数据类型的表，它与数字类型亲和性相关联，并且它的默认行为是在值没有小数时存储整数，或者如果值包含小数，则存储实数。这有助于节省字节存储，因为最常用的整数值(对于中小型应用程序小于一百万的值)需要比实际数据类型更少的字节:

整数(大约):

• ± 128 > 1 字节 (2^8-1)
• ± 32,768 > 2 字节 (2^16-1)
• ± 8,388,608 > 3 字节 (2^24-1)
• ± 2,147,483,648 > 4 字节 (2^32-1)
• ± 140,737,488,355,328 > 6 字节 (2^48-1)
• ± 9,223,372,036,854,780,000 > 8 字节 (2^64-1)

针对 8 字节真实 IEEE 数据类型

如果您需要始终存储实数，则有必要声明一个 float /实数数据类型，以便被视为实数类型关联，因此即使您向 SQLite 发送整数，它也会被存储为实数

来源:https://sqlite.org/datatype3.html

我知道 real 可能不精确，但我已经使用 C# Decimal 数据类型和 SQLite 以 wal 模式在磁盘中的数据库进行了一些测试，如果所有操作都是在 C# 中进行的，我从来没有遇到过舍入错误。但是当我直接在 SQLite 中进行一些计算时，由于 2 个整数除法，我得到了一些舍入错误和一些错误的计算

我使用 EF6 和 Dapper 读取/写入数值(来自 SQLite 的整数/实数)并且从未出现错误。

现在，我计划利用 SQLite 数字行为来使用小巧的 TypeHandler 节省磁盘空间，因此我可以复制 SQL Server Money 实现(在内部，SQL Server 保存一个 8 字节整数和当它加载到内存中时除以 10000):

public class NumericTypeHandler : SqlMapper.TypeHandler<decimal>
{
    public override void SetValue(IDbDataParameter parameter, decimal value)
    {
        parameter.Value = (long)(value / 10000);
    }

    public override decimal Parse(object value)
    {
        return ((decimal)value)/10000M;
    }
}

此外，我设置了 datetime UnixEpoch 实现以节省磁盘空间并提高性能

注意:

SQLite 可以在低需求的 asp.net 应用程序中处理数十个用户，诀窍是使用 pragmas 指令和其他东西调整 SQLite:

• WAL 模式:有助于管理更好的并发性和性能
• 页面大小:如果与文件系统的簇大小匹配，则可提高性能
• 共享缓存模式:在多个线程中访问时实现表锁而不是数据库锁
• Datetime UnixEpoch:节省秒数(4 字节)而不是文本 ISO 日期时间(“2012-12-21T17:30:24”= 20 字节)。
• 缓存大小:保存的 I/O 访问将最后访问的页面保留在内存中

我还在 C# 方面做了一些改进:

• 我为 DateTime 值制作了一个自定义 DateTime 结构，以便将数据保存为整数来覆盖 ToString 方法，并通过构造函数重载将数据作为 DateTime 加载到内存中。
• 我在 C# 中创建了一个自定义数字结构(如 DateTime 结构)，以通过覆盖 ToString 方法以美分形式节省资金，并通过构造函数重载以 Decimal 形式加载到内存中

避免保存不必要的数据(错误日志、电子邮件 html 文本)

如果您打算使用 SQLite 作为后端，我认为您应该使用像 EFx 或 Dapper 这样的 ORM。使用 dapper，您需要创建自己的微框架以节省开发时间(基本的 CRUD 功能和 Linq 查询转换)。

我找到的最好的工具是 https://github.com/linq2db/linq2db ，您使用 linq 以非常快的速度和 linq 的易用性访问数据库。并且可以制作 CRUD 功能。

如果这个答案有帮助我会很高兴

问候