如何使用MySql的IN子句

本文关键字：IN 子句 MySql 何使用 | 更新日期: 2023-09-27 18:03:23

我正试图找出如何使用MySql的条款与ASP。净c#。这是我的代码

var WebSites = string.Join(",", wsl.Select(x => "'" + x.DomainUrl + "'").ToArray());
string q = "select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN (@Url)";

当我不使用参数时，这段代码可以工作。当我包含参数时，我从查询中得不到任何结果。

下面是我的参数代码

cmd.Parameters.Add("@Urls", MySqlDbType.Text).Value = WebSites;

这里是完整的代码

 public static IList<Post> FindPostsByWebSiteList(IEnumerable<WebSite> wsl)
 {
        var pl = new List<Post>();
        var WebSites = string.Join(",", wsl.Select(x => "'" + x.DomainUrl + "'").ToArray());
        string q = "select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN (@Urls)";
        using (MySqlConnection con = new MySqlConnection(WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString()))
        {
            using (MySqlCommand cmd = new MySqlCommand(q, con))
            {
                cmd.Parameters.Add("@Urls", MySqlDbType.Text).Value = WebSites;
                con.Open();
                var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    var p = new Post();
                    p.Id = reader.GetInt32("Id");
                    p.Url = reader.GetString("Url");
                    p.Title = reader.GetString("Title");
                    p.Date = reader.GetDateTime("Date");
                    p.ImageUrl = reader.GetString("ImageUrl");
                    pl.Add(p);
                }
                return pl;
            }
        }
    }

如何使用MySql的IN子句

我找到了答案。在这里

    public static IList<Post> FindPostsByWebSiteList(string[] urls)
    {
        var pl = new List<Post>();
        var urlArray = urls.Select((x,y) => "@url" + y.ToString()).ToArray();
        var urlsJoined = string.Join(",", urlArray);
        string q = string.Format("select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN ({0})", urlsJoined);
        using (MySqlConnection con = new MySqlConnection(WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString()))
        {
            using (MySqlCommand cmd = new MySqlCommand(q, con))
            {
                for (int x = 0; x < urlArray.Length; x++)
                {
                    cmd.Parameters.Add(urlArray[x], MySqlDbType.Text).Value = urls[x];
                }
                con.Open();
                var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    var p = new Post();
                    p.Id = reader.GetInt32("Id");
                    p.Url = reader.GetString("Url");
                    p.Title = reader.GetString("Title");
                    p.Date = reader.GetDateTime("Date");
                    p.ImageUrl = reader.GetString("ImageUrl");
                    pl.Add(p);
                }
                return pl;
            }
        }
    }

您引用的是@Url而不是@Urls

可能是你的问题打错了

IN语句应该期望一个字符串数组，而您正在传递一个字符串

你的最终SQL看起来像这样:

select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN ('url1,url2,url3')
不是

select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN ('url1', 'url2', 'url3')

检查这个问题:

添加List到mysql参数

相关文章：