序列化和反序列化 SQL 查询
我有一个嵌入式设备，它保存内部表的列表。 我希望它能够将该表的状态与某些外部数据库同步以进行调试。 也就是说，每当我向某个结构数组添加一个元素时，我希望设备发出“INSERT INTO ...”命令。
然而，我通过 RS232 串行电缆发送数据，这使得发送显式 SQL 的开销不可接受。
由于我经常使用的SQL命令只有3种，所以我只能序列化这几种。 即INSERT INTO、DELETE FROM 和UPDATE。
我的总体想法是使用“压缩/可序列化”SQL 协议发送数据。 我们不会直接向 SQL 服务器发送命令，而是向自定义序列化 SQL 服务器发送命令，我将这样写：
- 我们将为每个数据库更改的简单操作（即 INSERT、DELETE、UPDATE）分配一个编号。 唯一可用的可序列化 SQL 命令是 INSERT INTO x ()、DELETE FROM x WHERE id=y。 我们只能更改
x和y。 - 首先在服务器上创建所有必要的表一次。 在服务器上保留一个哈希表，将每个表映射到一个数字。 这可以用普通 SQL 来完成，因为这只完成一次。
- 然后为每个表分配一个编号，确保服务器知道这个编号。
- 最后，每当我们希望执行 SQL 命令时，我们都会发送命令编号，然后是表编号，然后是数据长度，最后是数据。 服务器将根据表的描述找出实际数据的布局。
例如，
INSERT INTO temperature(temperature,location)
VALUES ((108,"chille"),(120,"usa"))
将被翻译为
[INSERT INTO id][2 data to send]
[byte of 108][6 bytes string "chille"]
[byte of 120][3 bytes "usa"]
和
DELETE FROM people (id,"bob") WHERE id=1 or id=2
将被翻译为
[DELETE id][2 data to send][byte of 1][byte 2]
因为 id 被定义为单字节整数。
是否有任何已知的协议/实施本着这种精神？
有人有更好的主意吗？
I'm having an embedded device which keeps a list of inner tables. I would like it to synchronize this table's state with some outside database for debugging purposes. That is whenever I add an element to a certain struct array I wish the device to issue an "INSERT INTO ..." command.
However I'm sending the data over an RS232 serial cable, which makes the overhead of sending explicit SQL unacceptable.
Since there are only 3 types of SQL commands I use a lot, I can only serialize these few. Namely INSERT INTO,DELETE FROM, and UPDATE.
The general idea I had in mind is to send data with a "compressed/serializable" SQL protocol. We won't send commands directly to SQL server but to a custom serialized-SQL server I'll write:
- We'll assign a number to each database-changing simple action (ie INSERT, DELETE, UPDATE). The only available serializable-SQL commands are
INSERT INTO x (),DELETE FROM x WHERE id=y. Where we can only changexandy. - At first create all necessary tables on the server once. Keep a hash table on the server that maps each table to a number. This can be done in plain SQL as this is only done once.
- Then assign a number to each table, make sure the server knows about this number
- Finally whenever we wish to execute an SQL command, we'll send command number, followed by table number, followed by data length followed by data. The server would figure out the layout of the actual data by table's description.
For example
INSERT INTO temperature(temperature,location)
VALUES ((108,"chille"),(120,"usa"))
Would be translated to
[INSERT INTO id][2 data to send]
[byte of 108][6 bytes string "chille"]
[byte of 120][3 bytes "usa"]
and
DELETE FROM people (id,"bob") WHERE id=1 or id=2
Would be translated to
[DELETE id][2 data to send][byte of 1][byte 2]
Since id is defined as a single byte integer.
Is there any known protocol/implementation in this spirit?
Does anyone have a better idea?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
大多数 DBMS 使用准备好的语句来执行此操作。 您准备一条语句，例如一条插入，然后仅使用相关参数执行它。 服务器（或客户端）为准备好的语句提供某种 ID（通常是整数，有时是字符串），客户端库可以根据需要重新执行它。
您的一些想法需要改进 - 特别是 DELETE 中的 OR 并不明显。 此外，您还需要定义“要发送的 N 个数据”是否标识多行或多个值，如果是多行，则如何标识该行中有多少个值。
Most DBMS do this with prepared statements. You prepare a statement, such as an insert, and then execute it with just the relevant parameters. The server (or client) gives the prepared statement some sort of ID (typically, an integer, sometimes a string), and the client-side library can re-execute it on demand.
Some of your ideas will need refinement - the OR in the DELETE is not obvious, in particular. Also, you'd need to define whether your 'N data to send' identifies a number of rows or a number of values, and if a number of rows, how do you identify how many values in the row.
您可能在这里进行了过早的优化，特别是考虑到这只是为了测试。 我将使用纯 SQL 实现该功能并查看其执行情况。
然后考虑改进它需要多少时间，并将其好处与您在这段时间内可以做的其他事情进行比较。 就像添加用户可能购买的功能一样。
You might be doing premature optimization here, especially considering this is just for testing. I'd implement the feature using plain SQL and see how it performs.
Then consider the amount of time you'd need to improve it, and compare the benefits with the other things you could do in that time. Like add a feature a user might buy.