执行SQL语句，如果sql语句中包含':='再赋值操作会报错

[hjkl950217]

原因：MYSQL是5.6无法使用freesql的父子表功能，所以找了一段查询父子表的SQL。其中用到了“:=”再给变量赋值。这种写法在Navicat中直接执行可以，但是用freesql去执行会报错

C#代码:

            return this.Orm.Select<PartitionInfo>()
                .WithSql(@"
            -- set @ids := 88, @l := 0;
            SELECT t1.*
            FROM
                (
                    SELECT
                        @ids AS _ids,
                        (
                            SELECT
                                @ids := GROUP_CONCAT(id)
                            FROM
                                partition_info
                            WHERE
                                FIND_IN_SET(ParentId, @ids)
                            # 表内查询条件（AND site_id = 2）

                        ) AS cids,
                        @l := @l + 1 AS LEVEL
                    FROM
                        partition_info
                       -- (SELECT @ids := 0, @l := 0) b
                    WHERE
                        @ids IS NOT NULL
                ) as t,
                partition_info as t1
            WHERE
                FIND_IN_SET(t1.id, t._ids)
            ORDER BY
                LEVEL DESC", new { ids = id, l = 0 })
                .ToList();

错误信息:"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= GROUP_CONCAT(id)\r\n FROM\r\n ' at line 11"

如果不传递变量，用拼sql的方式同样报错。同样的问题我在sqlsuger上也遇到过，同样的错误，目前也不知道如何处理。

请问如果一定要在sql中用到':='操作，应该如何做呢？想要搞定这个问题，遇到过非常多次这个问题了

最终解：连接字符串中添加‘AllowUserVariables=True’

[luoyunchong]

连接字符串，Allow User Variables 了解一下。

[hjkl950217]

我试试

[luoyunchong]

https://www.connectionstrings.com/mysql/

[hjkl950217]

问题依旧，加了参数后一样不行。修改为拼sql的方式也不行

            return this.Orm.Select<PartitionInfo>()
                .WithSql($@"
             set @ids := {id}, @l := 0;
            SELECT t1.*
            FROM
                (
                    SELECT
                        @ids AS _ids,
                        (
                            SELECT
                                @ids := GROUP_CONCAT(id)
                            FROM
                                partition_info
                            WHERE
                                FIND_IN_SET(ParentId, @ids)
                            # 表内查询条件（AND site_id = 2）

                        ) AS cids,
                        @l := @l + 1 AS LEVEL
                    FROM
                        partition_info
                       -- (SELECT @ids := 0, @l := 0) b
                    WHERE
                        @ids IS NOT NULL
                ) as t,
                partition_info as t1
            WHERE
                FIND_IN_SET(t1.id, t._ids)
            ORDER BY
                LEVEL DESC")
                .ToList();

连接字符串："server=127.0.0.1;port=3306;Database=nybusiness;Uid=root;Pwd=123;CharSet=utf8;Allow Zero Datetime=true;Pooling=true;Connection Lifetime=600;Max Pool Size=40;Min Pool Size=1;Connection Reset=false;Allow User Variables=True"

现在是报‘ set @ids := 90, @l := 0;’ 附近有语法错误

[hjkl950217]

现在可以了

            return this.Orm.Select<PartitionInfo>()
                .WithSql($@"
            SELECT t1.*
            FROM
                (
                    SELECT
                        @ids AS _ids,
                        (
                            SELECT
                                @ids := GROUP_CONCAT(id)
                            FROM
                                partition_info
                            WHERE
                                FIND_IN_SET(ParentId, @ids)
                            # 表内查询条件（AND site_id = 2）

                        ) AS cids,
                        @l := @l + 1 AS LEVEL
                    FROM
                        partition_info,
                       (SELECT @ids := {id}, @l := 0) b
                    WHERE
                        @ids IS NOT NULL
                ) as t,
                partition_info as t1
            WHERE
                FIND_IN_SET(t1.id, t._ids)
            ORDER BY
                LEVEL DESC")
                .ToList();

刚才在sql中设置变量报错应该是被freesql嵌套一层之后，语法错误。

[2881099]

指定id，查询所有子记录：

SELECT t1.*, t.*
FROM
(
  SELECT
    @ids AS _ids,
    (
      SELECT @ids := GROUP_CONCAT(Code) FROM d_district
      WHERE FIND_IN_SET(ParentCode, @ids)
    ) AS cids,
    @l := @l + 1 AS LEVEL
  FROM d_district, (SELECT @ids := '110000', @l := 0) b
  WHERE @ids IS NOT NULL
) as t,
d_district as t1
WHERE FIND_IN_SET(t1.Code, t._ids)
ORDER BY LEVEL

[2881099]

向上查怎么弄？

[hjkl950217]

之前到是找到一个由子查到父的：

set @r := 90, @l := 0;

SELECT *
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := ParentId FROM partition_info WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        partition_info m
    WHERE @r <> 0) T1
JOIN partition_info T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

[2881099]

本来想研究下 AsTreeCte 看能不能兼容 mysql 5.6。

public ISelect<T1> AsTreeCte<T1>(this ISelect<T1> that,
        Expression<Func<T1, string>> pathSelector = null,
        bool up = false,
        string pathSeparator = " -> ",
        int level = -1)

up 、level 可以解决。

pathSelector、pathSeparator 怎么解决？这两个是为了拼接某个字段，如：

中国 -> 湖北 -> 武汉

[hjkl950217]

母鸡啊。。要不先实现基本功能？ 不拼？

[2881099]

行为不一致，不好向使用者解释

[hjkl950217]

要不你直接在AsTreeCte的注释上面增加一个 不支持mysql5.6？

我想的就是在设计的时候增加这么个说明就行。。5.6不好搞得

[2881099]

v2.0.0 支持 AsTreeCte 对 MySql5.6 的兼容向下或向上查询，但不支持 pathSelector/pathSeparator

public class VM_District_Child
{
    [Column(IsPrimary = true, StringLength = 6)]
    public string Code { get; set; }
    public string Name { get; set; }

    [Column(StringLength = 6)]
    public string ParentCode { get; set; }

    [Navigate(nameof(ParentCode))]
    public List<VM_District_Child> Childs { get; set; }
}

1、向下查询所有子节点：

var t3 = fsql.Select<VM_District_Child>().Where(a => a.Name == "中国").AsTreeCte().ToList();
Assert.Equal(4, t3.Count);
Assert.Equal("100000", t3[0].Code);
Assert.Equal("110000", t3[1].Code);
Assert.Equal("110100", t3[2].Code);
Assert.Equal("110101", t3[3].Code);

 SELECT a.`Code`, a.`Name`, a.`ParentCode` 
FROM ( SELECT cte_tbc.cte_level, a.`Code`, a.`Name`, a.`ParentCode`
  FROM (
    SELECT @cte_ids as cte_ids, (
      SELECT @cte_ids := group_concat(`Code`) 
          FROM `D_District` 
          WHERE find_in_set(`ParentCode`, @cte_ids)
    ) as cte_cids, @cte_level := @cte_idcte_levels + 1 as cte_level
    FROM `D_District`, (
      SELECT @cte_ids := a.`Code`, @cte_idcte_levels := 0 
          FROM `D_District` a
      WHERE 1=1 AND (a.`Name` = '北京')
      LIMIT 1) cte_tbb
    WHERE @cte_ids IS NOT NULL
  ) cte_tbc, `D_District` a
  WHERE find_in_set(a.`Code`, cte_tbc.cte_ids) ) a 
ORDER BY a.cte_level DESC

2、向下查询所有父节点：

var t4 = fsql.Select<VM_District_Child>().Where(a => a.Name == "东城区").AsTreeCte(up: true).ToList();
Assert.Equal(3, t4.Count);
Assert.Equal("110101", t4[0].Code);
Assert.Equal("110000", t4[1].Code);
Assert.Equal("100000", t4[2].Code);

SELECT a.`Code`, a.`Name`, a.`ParentCode` 
FROM ( SELECT cte_tbc.cte_level, a.`Code`, a.`Name`, a.`ParentCode`
FROM (
    SELECT @cte_pid as cte_id, (SELECT @cte_pid := `ParentCode` FROM `D_District` WHERE `Code` = cte_id) as cte_pid, @cte_level := @cte_level + 1 as cte_level
    FROM `D_District`, (
      SELECT @cte_pid := a.`Code`, @cte_level := 0 
          FROM `D_District` a
      WHERE 1=1 AND (a.`Name` = '东城区')
      LIMIT 1) cte_tbb
) cte_tbc
JOIN `D_District` a ON cte_tbc.cte_id = a.`Code` ) a 
ORDER BY a.cte_level