c# - MySqlDataReader 不会从具有函数的视图中读取信息架构

Question

所以我对 MySqlDataReader 有一个非常奇怪的问题。

Read()当视图包含函数时，它不希望这样做。

这是我的代码的一部分：

public static ColumnInfoCollection GetColumnData(MySqlConnection connection, string tableName)
{
    const string CMD_COLUMN_INFO_FORMAT =
@"SELECT column_name, column_default, data_type, character_maximum_length, is_nullable, ordinal_position, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = '{0}';";

    // etc..

    try
    {
        // etc..

        using (var command = new MySqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection))
        {
            command.CommandType = CommandType.Text;
            using (var reader = command.ExecuteReader())
            {
                ColumnInfo columnInfo = null;

                while (reader.Read()) // Does not read when there's a function in the view!
                {
                    etc..

这是视图：

CREATE VIEW `View_RB_Bookings_User21` AS
    SELECT
        B.`client_ref` AS "Client Ref",
        B.`status` AS "Status",
        B.`booking_id` AS "Booking ID",
        B.`confirmation_number` AS "Confirmation #",
        B.`hotel_name` AS "Hotel Name",
        B.`booking_date` AS "Booking Date",
        B.`checkin` AS "Checkin Date",
        B.`checkout` AS "Check-out Date",
        CONCAT_WS(' ', `currency`, `amount`) AS "Amount",
        `GetRoomsJsonForBooking`(B.`id`, U.`id`) AS "Rooms"
    FROM booking B
    INNER JOIN supplier S ON S.`id` = B.`supplier_id`
    INNER JOIN supplier_user SU ON SU.`supplier_id` = S.`id`
    INNER JOIN user U ON U.`id` = SU.`user_id`
    WHERE U.`id` = 21

如果我删除最后一列， Read() 工作..如果我把它留在里面， Read() 被跳过..

我手动运行了信息模式查询（如上所示），它按预期返回详细信息。“房间”的 data_type 为文本。

这里发生了什么，更重要的是：我该如何解决这个问题？

PS - 不确定是否重要，但 MySql.Data 包版本是 8.0.27

更新：在这里添加了函数定义以防万一：

CREATE DEFINER=`<Removed>`@`%` FUNCTION `GetRoomsJsonForBooking`(bookingId int, userId int) RETURNS text CHARSET utf8
BEGIN
    DECLARE result text;
    SELECT
        JSON_ARRAYAGG(JSON_OBJECT
        (
            '#', RoomDetail.`#`,
            'Status', RoomDetail.`Status`,
            'Name', RoomDetail.`RoomName`,
            'BoardBasis', RoomDetail.`BoardBasis`,
            'Price', RoomDetail.`Price`,
            'Pax', RoomDetail.`Pax`
        )) INTO result
    FROM HotelOffer.booking B
    INNER JOIN
    (
        SELECT
            BR.`booking_id` AS `BookingId`,
            BR.`room_idx` AS `#`,
            BR.`status` AS `Status`,
            BR.`more_info` ->> '$.room_name' AS `RoomName`,
            BR.`more_info` ->> '$.board_basis' AS `BoardBasis`,
            SUM(BR.`more_info` ->> '$.price') AS `Price`,
            JSON_ARRAYAGG(JSON_OBJECT
            (
                'Title', BRP.`title`,
                'FirstName', BRP.`first_name`,
                'LastName', BRP.`last_name`,
                'Age', BRP.`age`,
                'Type', BRP.`type`,
                'Nationality', BRP.`nationality`
            )) AS `Pax`
        FROM HotelOffer.`booking` B
        INNER JOIN HotelOffer.`booking_room` BR ON BR.`booking_id` = B.`id`
        INNER JOIN HotelOffer.`booking_room_pax` BRP ON BRP.`booking_room_id` = BR.`id`
        INNER JOIN HotelOffer.supplier S ON S.`id` = B.`supplier_id`
        INNER JOIN HotelOffer.supplier_user SU ON SU.`supplier_id` = S.`id`
        WHERE BR.`booking_id` = bookingId
        AND SU.`user_id` = userId
        GROUP BY BR.`booking_id`, BR.`room_idx`, BR.`status`, `RoomName`, `BoardBasis`
    ) AS RoomDetail ON RoomDetail.`BookingId` = B.`id`
    INNER JOIN HotelOffer.supplier S ON S.`id` = B.`supplier_id`
    INNER JOIN HotelOffer.supplier_user SU ON SU.`supplier_id` = S.`id`
    INNER JOIN HotelOffer.user U ON U.`id` = SU.`user_id`
    WHERE U.`id` = userId
    AND B.`id` = bookingId;
RETURN result;
END

标签： c#mysqlmysql-connector