T-SQL的timestamp类型实际应用
目录
0x00 适用场景
0x01 问题描述
0x02 字节数组
0x03 Base64编码
0x04 其实没那么麻烦
0x05 回顾
0x00 适用场景
1. 前端: JavaScript
2. 后端: ASP.NET Core C#
3. 数据库: Microsoft SQL Server
4. SQL Server中的timestamp数据类型可以理解为时间戳, 但是这个意思会引起很多误解, 这个误解跟中文翻译无关. Stack Overflow上也有很多人对timestamp这个单词产生误解, 认为可以从这个类型的数据中得到日期时间的信息. 最初, 为了解决数据库记录在update时因并发而产生的问题, 微软在数据库中添加了时间戳这个类型. 但是其实这只是一些字节数组, 并且记录添加或修改的先后会影响这个数组中的数值大小. 其实更为准确地理解这个类型, 应该把它看成是行版本号, 因此后续微软又引入了它的同义词rowversion.
0x01 问题描述
公司的每张表都有RowTS这个字段, 并且数据类型为timestamp. 在服务器端, 使用Dapper来做数据访问. 当insert一条记录时, 由数据库自动生成RowTS值, 当select这条记录时, 在SSMS查询结果中显示的是这种值: 0x00000000001CB574, 于是同事在C#的Model中将该字段类型设置为string. string类型在C#和JavaScript之间通过json字符串传递是没有任何问题的, 但是在update这条记录时, 要把这个值作为where条件时就不行了, 数据库会提示如下错误信息: "Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.".
经过同事在SSMS中尝试, 发现不把这个值作为字符串(用单引号括起来), 而是直接作为字面量, 就不会报错. 那么在C#中就是直接拼SQL字符串, 而不是用参数即可. 其实在select查询时, 还需要对值进行转换(转换表达式为CONVERT(NVARCHAR, CAST(Table.RowTS AS BINARY(8)), 1) ), 否则传递到C#中时, Dapper会对它进行类型的自动转换, 转换后的值并不是SSMS中显示的那个样子.
那么, 这个timestamp数据类型到底对应C#的哪个类型? Newtonsoft的JSON序列化器把值转成的字符串为什么跟SSMS中显示的不一样? SSMS显示的那个值是一个什么表现形式? timestamp这种数据类型就真的不能在C#端做参数化查询吗?
0x02 字节数组
让我们顺着线索一点一点来找, 首先要弄明白前面提到的第一个问题 ---- T-SQL的timestamp对应C#的哪个类型? 经过验证, 它不是字符串类型, 当把属性的类型设置为字符串时, 调试时会抛异常. Dapper很明确地指出是RowTS列错误的转换, 并且标识出转换前的类型System.Byte[] - Object. 那么这可以说明timestamp就是字节数组类型, 而在SSMS查询结果中看到的则是某种数据类型的表示形式. 其实用C / C++写底层的同学会对这种类型及表示形式比较熟悉些, 这是字节数组的十六进制表示形式. 首先, 0x起头代表这是十六进制表示法, 同时0x跟数据没关系, 0x后面的才是数据; 其次一个字节需要用2位的十六进制数来表示, 数数后面一共几个2位, 很明显是8个, 那么也就是说这个字节数组的长度为8, 大家也可以看到上面那个T-SQL的转换表达式中BINARY(8)中的8就是这么来的; 然后0x这种十六进制表示法在C#中可以直接作为字面量, 就跟写二进制数21623是一样的, 而且如果想把一个整数显示成十六进制表示, 在ToString方法中加入一个格式参数X2即可, 比如刚才说的21623, 用十六进制表示就是0x5477(很遗憾没见到字母); 最后前面提的那些问题, 我们现在搞清楚了一半. T-SQL的timestamp对应C#的字节数组类型, C#使用Dapper时实体对应的属性类型必须是字节数组, 而不能是字符串, 在SSMS查询结果中看到的是, 字节数组的每个元素转换为十六进制表示法的字符串, 并拼接而成, 我同事用的转换表达式就是完成SSMS把timestamp数据转换为nvarchar数据的功能, 而nvarchar要用C#的字符串来接收, 这样的话, 参数化查询就不成问题了, 对于timestamp这种字段, 只需要为它的参数传入一个字节数组(注意这个字节数组如果不是从数据库中查出来的, 还要保证是8的长度)即可. 剩下的另一半问题就是为什么Newtonsoft的JSON序列化器转换后的字符串, 也就是我们在前端接收到的值跟十六进制表示法的字符串不一样呢? 接着看下一节.
0x03 Base64编码
前面曾经提到一个十六进制表示法的值0x00000000001CB574, 我们就拿这个值接着举例. 如果用byte数组接收这个值, 然后再进行json序列化, 传递到前端浏览器. 我们会发现浏览器收到的数据是个字符串, 并不是数组(尽管js有数组这个类型), 这个字符串是这样的: AAAAAAActXQ=. 这个例子比较极端, 生成的Base64编码没有数字, 其实是可以有数字和字母的, 然后=是用来补足位的. 我粗略看了一下Base64编码(因为平常工作中并不需要我去实现编码代码, 而是直接调用各种语言提供的库函数即可), 它其实是把计算机底层存储的数据(只有0和1)以位(bit)为基础, 几个几个地编成一组, 进行多次转换后生成的字符串. 由于有时最后一组的数量不够, 不够的就用二进制的0来占位, 最终用=来标识这些补位的0. 它的第一个用途就是简单"加密", 人的肉眼不可能很直接明了地看出Base64编码与编码前的数据的对应关系(但是用程序还是能解码的); 第二个就是缩小二进制数据转为字符串所占用的长度, 二进制的一个字符只能表示2个数, 如果数据很大, 字符串的长度会很长, 但是进行Base64编码后, 得到的字符串就回很短. 正是由于这个特点, Base64编码被用来作为网络传输的编码格式, 最常用的是图片数据. 而这里8个长度的字节数组也被序列化成Base64编码的字符串, 然后传递json字符串给浏览器. 最后经过验证, C#把字节数组的属性, 序列化成Base编码的json字符串, 传递给JavaScript; 而当JavaScript把这个Base64编码字符串原封不动的传回给C#, C#的json字符串饭序列化会自动把它还原为字节数组, 因此C#这边依然可以用那个字节数组类型的属性来接收. 现在C#到前端js的处理也搞清楚了. 下面把两部分结合到一起, 做个总结.
0x04 其实没那么麻烦
我的同事对于timestamp类型的数据处理有些麻烦了. 其实T-SQL那边不用Convert转换表达式, 直接查询字段即可. 而到了C#里, 只需要把对应的属性的数据类型设置为byte[], 即能正确接收, 然后在C#传递json字符串时, 也不用特殊处理, 直接把byte[]交给json序列化器. 到了前端js中, 由于RowTS是作为标识数据的版本, 因此这个数据值一定不会被更改, 也不允许更改. 只需要在向服务器发送请求时, 仍然原样带着这个数据即可, C#那边仍然接收为字节数组类型(json反序列化器把Base64编码的字符串给转换为了字节数组). 最后在执行update语句时, 也不要做任何转换或处理, SQL语句中直接用参数, 在Dapper中直接用字节数组给这个参数赋值, 就圆满完成了.
0x05 回顾
一开始提出的问题: 数据表中有timestamp类型的字段, 在C#端和js端如何处理?
这个问题我就是这么解决的, SQL语句查询时直接查该字段, C#用byte[]来接收(这里用Dapper), 然后C#再把byte[]传给js(这里用Newtonsoft), js端对收到的值不要做任何改动, 再原样传回, C#端直接用byte[]来接收, 最后SQL语句更新记录时, where条件中的参数用byte[]来赋值, 这样就可以了.
另外, 这里涉及几个概念, 我没有展开讲解, 其实我也没了解十分透彻, 因为不大用得到, 而且即使用到, 也可以在网上查资料. 大家有兴趣的可以回去自己研究, 如下:
1. T-SQL的timestamp类型
2. 字节数组的十六进制表示法
3. Base64编码
如果本文中有哪里写的不对, 请您斧正!
