Csharp 为什么 SQL 'WHERE' 子句中的 Dapper 参数区分大小写

出身背景

正如Jeroen所指出的那样:

Presumably Dapper isn't doing any such thing and the same thing happens from any language where a parameter is passed as a regular string...

这确实不是Dapper的问题，而是SQL数据类型的问题.recipeExample数据库中的name列不知道传入的数据类型应该是citext类型.因此，有必要将传入参数强制转换为citext.

As Jeroen还指出:

From what I gather Postgres also supports collations, and using a case-insensitive collation on a regular string type is likely to work without conversion of any kind.

不知怎的，我错过了这个，但pgDocs甚至建议考虑nondeterministic collations而不是使用citext模块.在阅读了localization、collations和this YouTube video之后，我更新了recipe example web api，以使用citext模块与不确定排序进行比较.

数据库更新

首先，我添加了PostgreSQL文档中提供的case_insensitive排序规则:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

然后，我更新了recipe表，使其具有两个名称列:使用case_insensitive排序规则的text类型的name_1列和citext类型的name_2列:

+-------------+--------+------------------+----------+--------------------+
|   Column    |  Type  |    Collation     | Nullable |      Default       |
+-------------+--------+------------------+----------+--------------------+
| recipe_id   | uuid   |                  | not null | uuid_generate_v4() |
| name_1      | text   | case_insensitive | not null |                    |
| name_2      | citext |                  | not null |                    |
| description | text   |                  |          |                    |
+-------------+--------+------------------+----------+--------------------+
Indexes:
    "recipe_pkey" PRIMARY KEY, btree (recipe_id)
    "recipe_name_citext_key" UNIQUE CONSTRAINT, btree (name_2)
    "recipe_name_key" UNIQUE CONSTRAINT, btree (name_1)

接下来，我创建了三个Postgres函数来测试"Meatballs"查询:

1. 第一个函数查询name_1列并接受text参数
2. 第二个函数查询name_2列并接受text参数
3. 第三个函数查询name_2列并接受citext参数

CREATE FUNCTION getrecipe_name1_text(text) RETURNS recipe as $$
    SELECT *
    FROM recipe
    WHERE recipe.name_1 = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION getrecipe_name2_text(text) RETURNS recipe as $$
    SELECT *
    FROM recipe
    WHERE recipe.name_2 = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION getrecipe_name2_citext(citext) RETURNS recipe as $$
    SELECT *
    FROM recipe
    WHERE recipe.name_2 = $1;
$$ LANGUAGE SQL;

查询测试

查询包含text个参数的name_1列:

recipeexample=# SELECT * FROM getrecipe_name1_text('Meatballs');

+--------------------------------------+-----------+-----------+----------------+
|              recipe_id               |  name_1   |  name_2   |  description   |
+--------------------------------------+-----------+-----------+----------------+
| 8f749e7a-e192-48df-91af-f319ab608212 | meatballs | meatballs | balled up meat |
+--------------------------------------+-----------+-----------+----------------+
(1 row)

查询包含text个参数的name_2列:

recipeexample=# SELECT * FROM getrecipe_name2_text('Meatballs');

+-----------+--------+--------+-------------+
| recipe_id | name_1 | name_2 | description |
+-----------+--------+--------+-------------+
|           |        |        |             |
+-----------+--------+--------+-------------+
(1 row)

查询包含citext个参数的name_2列:

recipeexample=# SELECT * FROM getrecipe_name2_citext('Meatballs');

+--------------------------------------+-----------+-----------+----------------+
|              recipe_id               |  name_1   |  name_2   |  description   |
+--------------------------------------+-----------+-----------+----------------+
| 8f749e7a-e192-48df-91af-f319ab608212 | meatballs | meatballs | balled up meat |
+--------------------------------------+-----------+-----------+----------------+
(1 row)

结论

1. 如果使用citext模块，查询时参数必须转换为citext
2. 如果使用case_insensitive排序规则，将有性能损失，并且不可能进行模式匹配操作