分隔字段的逗号分隔值并将它们添加到临时表的整数字段中

Question

我有一个表 member_details ，其中包含字段“preferred_location”（varchar），该表具有逗号分隔的值，例如来自列表框选择的“19,20,22”......

现在我还有另一个表 city_master ，其字段为“city_id”（int ）和“city_name”（varchar）...

现在我想分离“preferred_location”（varchar）值并将它们添加到临时表的整数字段中，这样我就可以在临时表的 city_id(int) 和city_master 的 city_id(int) ，然后可以从 city_master 的 city_name 获取城市名称...

这就是我在 MySQL 中需要的所有东西 - 存储过程或函数。我将它与 c#.net 一起使用。

Answer 1

坦率地说，这听起来是一个糟糕的设计。如果您需要单独的整数值，请相应地修改数据库结构，并首先单独保存这些值。

我的意思是，您知道它会导致什么结果 - 因为您将值作为列表存储在字符串中，所以您已经将自己调整到每次想要连接表时都需要展开值的位置。

这就像把马放在马车后面一样。

Answer 2

如果这些整数很小，例如 19、20、22 等，则只需使用较小的 16 或 8 位整数（如数据库支持的），并且它不应该比字符串占用更多的空间（可能甚至更少）。

Answer 3

制作了一些模拟示例，但这也应该适用于 LinqToMySql。

class user {
public string name {get;set;}
public int id {get;set;}
}
class member_detail {
public int user_id {get;set;}
public string prefered {get;set;}
}

class city_master{
public int code {get;set;}
public string name {get;set;}
}
void Main()
{
var users = new List<user>();
users.Add(new user(){name = "Mary",id = 1});
users.Add(new user(){name = "John",id=2});
    var details = new List<member_detail>() ;
    details.Add(new member_detail(){user_id=1,prefered="1,2,3"});
    details.Add(new member_detail(){user_id=2,prefered="3,5"});
    var cities = new List<city_master>();

    cities.Add(new city_master(){code =1,name="Moscow"});
    cities.Add(new city_master(){code =2,name="London"});
    cities.Add(new city_master(){code =3,name="Paris"});
    cities.Add(new city_master(){code =4,name="Rome"});
    cities.Add(new city_master(){code =5,name="Madrid"});

    users.Select(u=>new {u.name,cities=
    details.Where(d=>d.user_id==u.id)
    .SelectMany(d=>d.prefered.Split(','))
    .Join(cities,c=>c,d=>d.code.ToString(),(a,b)=>new {b.name})}).Dump();

}

Answer 4

感谢您的建议，但就我而言，最好将首选位置城市的 ID 存储为逗号分隔。

我有一个创建临时表的过程，然后我可以使用 city_master 表的内部联接来获取城市名称。

Create Procedure parseAndStoreList(in thingId int, in i_list varchar (128), 
                                   out returnCode smallInt) 
BEGIN 
         DECLARE v_loopIndex default 0;
         DECLARE Exit Handler for SQLEXCEPTION 
BEGIN
        call saveAndLog(thingId, 'got exception parsing list');
        set returnCode = -1;
END;

call dolog(concat_ws('got list:', i_list)); 
          pase_loop: LOOP set v_loopIndex = v_loopIndex + 1; 
call dolog(concat_wc(',', 'at loop iteration ', v_loopIndex);
          LOOOP parse_loop; 
set returnCode = 0;
END;