十进制类型从内存Sqlite数据库中读取为双精度或整型

本文关键字：读取 双精度 整型 数据库 类型 内存 Sqlite 十进制 | 更新日期: 2023-09-27 18:13:14

我在。net中没有太多的Sqlite经验，但是我看到的行为相当奇怪。假设我们有一个。net核心应用，project.json:

{
  "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:解析错误第2列(价格=1.4 -双)->系统。Invali dCastException:无法强制转换"系统"类型的对象。输入'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

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

十进制类型从内存Sqlite数据库中读取为双精度或整型

在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/real数据类型，以便被认为是实数类型的亲和性，这样即使你发送一个整数给SQLite，它也会被存储为实数

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

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

我使用EF6和Dapper读/写数值(整数/实数从SQLite)，从来没有得到一个错误。

现在，我计划利用SQLite数字行为节省磁盘空间与dapper 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 Datetime ("2012-12-21T17:30:24" = 20字节)。
  • 缓存大小:保存的I/O访问将最后访问的页面保存在内存中

我还在c#方面做了一些增强:

  • 我为DateTime值做了一个自定义DateTime结构，以便将数据保存为整数覆盖ToString方法，并通过构造函数重载加载到内存中作为DateTime。
  • 我在c#中创建了一个自定义的Numeric结构体(如DateTime结构体)，以节省重写ToString方法的费用，并通过构造函数重载以Decimal形式加载到内存中

避免保存不必要的数据(错误日志，email html文本)

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

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

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

对

这是SQLite的一个特性，请参阅此链接:https://sqlite.org/faq.html#q3

相关文章：