什么是"FOR XML AUTO"?在MySQL中? (用于具有表示行的XML字符串) [英] What is the equivalent of "FOR XML AUTO" in MySQL? (for having an XML string that represents a row)

问题描述

在SQL Server中，我经常使用"FOR XML AUTO"，它允许诸如"SELECT car from XML FOR XML AUTO"之类的东西返回< car>< id> 1</id></car>< ; car>< id> 2</id></car>".有没有办法在MySQL中做到这一点?

我想要的是能够将此XML存储在另一个表中，这样我就可以完整地记录数据库中所做的更改.历史记录表将由触发器填充，每一行将是更改过的行的非规范化表示.存储XML似乎是更改表的定义的一种好方法，而不必担心弄乱日志历史记录.因此，我需要一种简单而直接的方式来拥有可以作为数据值插入的字符串，并且该字符串必须是该行的XML描述.

非常感谢！
问候，

In SQL Server I frequently use "FOR XML AUTO", which allows something like "SELECT id FROM car FOR XML AUTO" to return "<car><id>1</id></car><car><id>2</id></car>". Is there a way to do this in MySQL?

What I want is to be able to store this XML in another table so I can have complete log of changes made in the database. The history table will be populated by triggers, and each row will be a denormalized representation of the row that changed; storing XML seems like a great way to change the definition of a table and not worry about messing up the log history. So what I need is a simple and straightforward way to have an string that can be inserted as a data value, and this is string must be the XML description of the row.

Thank you very much!
Regards,

佩德罗

推荐答案

请参见 http://www.mysqludf.org/lib_mysqludf_xql/index.php ，用于来自MySQL UDF存储库的lib_mysqludf_xql库.

See http://www.mysqludf.org/lib_mysqludf_xql/index.php for the lib_mysqludf_xql library from the MySQL UDF Repository.

该库提供了一组函数，这些函数可以生成类似于SQL/XML的XML输出，如Oracle/SQL Server中所示.页面上有下载/构建/安装说明，以及对该库及其提供的各种功能的描述.

The library provides a set of functions that generate XML output similar to SQL/XML as seen in Oracle/SQL Server. There are download/build/install instructions on the page along with a description of the library and the various functions it provides.

这些应该足以在您计划中的触发器中使用.

These should be sufficient to be used from within a trigger as you were planning.

-Dipin

这篇关于什么是"FOR XML AUTO"?在MySQL中? (用于具有表示行的XML字符串)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！