主页 / dba / 问题 / 126531
Accepted
Jonathan Parent Lévesque
Asked: 2016-01-19 07:23:49 +0800 CST

用于软件间数据交换的 mysql 自定义查询日志记录

  • 772

大约 2-3 年，我在 SQL Server 中编写了一些触发器（每个表一个），允许我在我们的主要软件中记录查询。

ALTER TRIGGER [dbo].[SUIVI_REQUETE_CAT_CATBRUT] ON [dbo].[CAT_CATBRUT] AFTER INSERT, DELETE, UPDATE

AS
DECLARE
  @date as nvarchar(10),
  @heure as nvarchar(15),
  @nomUtilisateur as nvarchar(50),
  @poste as nvarchar(50),
  @requete as nvarchar(MAX)
BEGIN
  set @requete = 'DBCC INPUTBUFFER(' + str(@@SPID) + ')'
  
  CREATE TABLE temporaire (EventType nvarchar(30), Parameters int, EventInfo nvarchar(MAX))
  insert into temporaire exec(@requete)
  
  set @requete = (SELECT EventInfo FROM temporaire)
  drop table temporaire
  
  set @poste = (SELECT HOST_NAME() AS HostName)
  set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)
  set @date =(SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD])
  set @heure =(select CONVERT(VARCHAR,getdate(),108))
  
  INSERT INTO INF_LOG_REQUETE (REQUETE, POSTE, NOM_UTILISATEUR, [DATE], HEURE) VALUES (LEFT(@requete, 4000), @poste, @nomUtilisateur, @date, @heure)
End

我现在是一个新网络部门的负责人，我们的目标之一是让我的网络软件与我们的主要软件共享数据。我想在两个软件之间共享的表（客户、车辆、员工、日历等）中保存一些关键行。

关于我的问题，我有三个问题：

  1. 我只想记录特定数据库用户的查询（我们不想陷入来回发送相同修改的无限循环）。在 SQL Server 中，我用它来检测用户：

    set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)

是否可以检测到在 MySQL 中执行查询的用户？

  1. 在 SQL Server 中，我能够对插入、更新和删除操作使用相同的触发器。

是否可以在 MySQL 中做同样的事情，或者我是否必须从每个表中编写三个不同的触发器？

  1. 如果同一个触发器可以用于多个操作（插入、更新、删除），是否可以检测哪个操作（插入、更新、删除）启动了触发器？

附加说明：我只想在表中记录特定字段（包含要共享的关键数据）。我真的很想为每个表编写一个触发器，这样 PHP 函数就可以自动用数据解析字段并构建一个 XML 文件。例如，我想在我的日志表中包含一个字段：

{table}:[vehicle], {action}:[insert], {field}:[make], {value}:[acura], {field}:[model], {value}:[S7], ...

并生成如下所示的 XML：

<LIST>
    <QUERY>
        <TABLE>vehicle</TABLE>
        <ACTION>insert<ACTION>
        <FIELD>make</FIELD>
        <VALUE>acura</VALUE>
        <FIELD>model</FIELD>
        <VALUE>S7</VALUE>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
</LIST>

谢谢

mysql trigger

1 个回答

  1. Best Answer

    好的，经过一些研究，这是我找到的答案。

    Q1 :是否可以检测到在 MySQL 中执行查询的用户？

    可以通过以下方式检测哪个用户：select user();

    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user

    当心：在触发器和事件中current_user()返回定义对象的用户（在 SP 和视图中，除非使用 定义SQL SECURITY INVOKER）。您确实需要使用该user()函数在这些上下文中返回调用程序。

    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

    Q2：是否可以在同一个触发器上触发多个操作（INSERT、UPDATE、DELETE）？

    最不幸的是，在当前时间 (2016/02/01)，不可能在 MySQL 中同时触发两个操作。我们能做的最好的事情就是在存储过程中重新组合公共编码。

    以下过程是解决此问题的一种可能方法。它期望输入：

    1. callingTable：我们要记录查询的表的名称（必须在白名单中）。
    2. action：“删除”、“插入”或“更新”。

    3. fieldsValues: 包含一对有效的 XML 字段/值的字符串。举个例子：<field>ID</field><value>1616</value>

      CREATE PROCEDURE `dataLinkRecorder` (
IN callingTable VARCHAR (25),
IN action ENUM('DELETE', 'INSERT', 'UPDATE'),
IN fieldsValues TEXT
)
BEGIN
DECLARE callingUser VARCHAR(25) DEFAULT '';
DECLARE fieldCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE fieldCCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCCount SMALLINT UNSIGNED DEFAULT 0;

IF (TRIM(fieldsValues) <> '') THEN

    /* the calling table must be one in the white list */
    SELECT CASE 
        WHEN UPPER(callingTable) IN (
            'CATEGORY',
            'CUSTOMER',
            'MAKE',
            'MODEL',
            'PRODUCT',
            'VEHICLE'
        )
        THEN TRIM(UPPER(callingTable))
        ELSE '' 
        END AS myTable
        INTO callingTable;

     SELECT TRIM(
     UPPER(
        IFNULL(USER(), 'CLIENT')
     )
     ) AS myUser
INTO callingUser;

/* do not record modifications from tier software */
IF (callingTable <> '' AND INSTR(callingUser, 'CLIENT') < 1) THEN

    SELECT IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<field>', ''))) / 7)
    , 0) AS fCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</field>', ''))) / 8)
    , 0) AS fCCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<value>', ''))) / 7)
    , 0) AS vCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</value>', ''))) / 8)
    , 0) AS vCCount
    INTO fieldCount, fieldCCount, valueCount, valueCCount;

    /* validate the field/value XML pair */
    IF (fieldCount > 0 AND
        valueCount > 0 AND
        fieldCount = fieldCCount AND
        valueCount = valueCCount AND
        fieldCount = valueCCount
    ) THEN

        INSERT INTO `pc_datalink` 
            (`table_name`, `query_xml`) 
        VALUES(
            callingTable,
            CONCAT(
                '<query>',
                    '<table>', callingTable, '</table>',
                    '<action>', action, '</action>',
                    fieldsValues,
                '</query>'
            )
        );
    END IF;
END IF;
END IF;
END //

    将在下表中插入值：

    CREATE TABLE `pc_datalink` (
    `id` int(10) UNSIGNED NOT NULL,
    `table_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `query_xml` text COLLATE utf8_unicode_ci NOT NULL,
    `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `AK_DATE_ADDED` (`date_added`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    例如，一个像这样的非常简单的触发器：

    CREATE TRIGGER `dataLinkDeleteVehicle` AFTER DELETE ON `pc_vehicle`
FOR EACH ROW
BEGIN
IF (OLD.original_id <> '') THEN

    CALL dataLinkRecorder(
        'VEHICLE',
        'DELETE',
        CONCAT(
            '<field>ID</field><value>',
            OLD.original_id,
            '</value>'
        )
    );
END IF; 
END //

    将在表中生成一行，如下所示：

    在此处输入图像描述

    如果您仍有疑问，请随时询问详情，

    祝大家成功

    • 0

相关问题