nobsvalo's questions

我试图建立一个正确的字符串作为参数传递给一个简单的查询。

返回记录的正确查询：

SELECT id_s FROM group_of_id_s
WHERE date IN ('2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04');

...但如果我通过这个 (''2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04'' ) 作为参数它不起作用。

我也试图通过这个（'2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04'）

...但它也不起作用。

我的想法是通过一个过程把这个字符串作为参数。或许是这样的：

IN parameter TEXT

BEGIN

SET @c = CONCAT('SELECT id_s FROM group_of_id_s
WHERE date IN (', '''' , parameter, '''', ')');

PREPARE stmt from @c;
EXECUTE stmt;

END

任何参考或有用的建议将不胜感激。

编辑：基于来自@nbk 的非常有用的示例并在将其与数组进行比较之前更改日期格式，我设法提出了一个有效的查询，就像一个魅力。您可以在下面看到它的所有荣耀。;O) 来自@nbk 的响应因此被视为解决方案。非常感谢@nbk。

SELECT id_s FROM group_of_id_s
WHERE find_in_set(DATE_FORMAT(`date`, '%Y-%m-%d'), parameter) > 0;

Error: Code 1054. Unknown column 'U2.id_naslov' in 'field list'在 MySQL Workbench 中的这个简单查询中被抛出：

UPDATE krneki_1 AS U1, krneki_2 AS U2 
SET U1.id_naslov = U2.id_naslov
WHERE (U2.id_zaposlen = U1.id_naslovi_zaposleni)

我在网上搜索并阅读了其他帖子，但没有任何帮助......

我想这是一个微不足道的解决方案，但我只是看不到它。

TSQL（sql server）上从未出现过这种错误。

表 krneki_2 是由 Mysql 工作台通过数据导入（创建新表）创建的，后来发生此错误时，我还将数字字段更改为 smallint，只是为了看看它是否有帮助......但是......没有。

结果SHOW CREATE TABLE krneki_2：

       Table: krneki_2 
Create Table: CREATE TABLE `krneki_2` 
( `id` smallint(6) NOT NULL AUTO_INCREMENT, 
  `id_naslov` smallint(6) NOT NULL, 
  `id_zaposlen` smallint(6) NOT NULL, 
  PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8 

1 row in set (0.00 sec)

结果SHOW CREATE TABLE krneki_1：

       Table: krneki_1
Create Table: CREATE TABLE `krneki_1` (
  `id_naslovi_zaposleni` smallint(6) NOT NULL AUTO_INCREMENT,
  `id_naslov` smallint(6) DEFAULT NULL,
  `id_zaposleni` smallint(6) DEFAULT NULL,
  `id_aktiven` tinyint(4) DEFAULT '0',
  `cas_vnosa` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_naslovi_zaposleni`)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果来自information_schema，特别是来自评论中建议的此查询：

select
  table_catalog, table_schema, table_name, column_name, ordinal_position
from information_schema.columns
where table_name like '%krneki_1%' and column_name like '%naslov%' ;

结果krneki_1和naslov：

+---------------+--------------+-------------+----------------------+------------------+
| table_catalog | table_schema | table_name  | column_name          | ordinal_position |
+---------------+--------------+-------------+----------------------+------------------+
| def           | hq_db        | krneki_1    | id_naslovi_zaposleni |                1 |
| def           | hq_db        | krneki_1    | id_naslov            |                2 |
+---------------+--------------+-------------+----------------------+------------------+
2 rows in set (0.00 sec)

结果krneki_2和naslov：

+---------------+--------------+-------------+--------------+------------------+
| table_catalog | table_schema | table_name  | column_name  | ordinal_position |
+---------------+--------------+-------------+--------------+------------------+
| def           | hq_db        | krneki_2    | id_naslov    |                2 |
+---------------+--------------+-------------+--------------+------------------+
1 row in set (0.00 sec)

结果krneki_2和zaposlen：

+---------------+--------------+-------------+--------------+------------------+
| table_catalog | table_schema | table_name  | column_name  | ordinal_position |
+---------------+--------------+-------------+--------------+------------------+
| def           | hq_db        | krneki_2    | id_zaposlen  |                3 |
+---------------+--------------+-------------+--------------+------------------+
1 row in set (0.00 sec)

进一步挖掘，如建议的那样：

select 
  table_catalog, table_schema, table_name, column_name, ordinal_position,         
  char_length(column_name) as cl, length(column_name) as l
from information_schema.columns 
where table_name = 'krneki_2' ;

结果krneki_2：

+-------------+------------+----------+-----------+----------------+---+---+-------------+
|table_catalog|table_schema|table_name|column_name|ordinal_position| cl| l | column_type |
+-------------+------------+----------+-----------+----------------+---+---+-------------+
| def         | hq_db      | krneki_2 |id         |              1 |  2|  2| smallint(6) |
| def         | hq_db      | krneki_2 |id_naslov  |              2 | 10| 12| smallint(6) |
| def         | hq_db      | krneki_2 |id_zaposlen|              3 | 11| 11| smallint(6) |
+-------------+------------+----------+-----------+----------------+---+---+-------------+
3 rows in set (0.00 sec)

结果krneki_1：

+-------------+------------+----------+--------------------+----------------+--+--+-----------+
|table_catalog|table_schema|table_name| column_name        |ordinal_position|cl| l|column_type|
+-------------+------------+----------+--------------------+----------------+--+--+-----------+
| def         | hq_db      | krneki_1 |id_naslovi_zaposleni|              1 |20|20|smallint(6)|
| def         | hq_db      | krneki_1 |id_naslov           |              2 | 9| 9|smallint(6)|
| def         | hq_db      | krneki_1 |id_zaposleni        |              3 |12|12|smallint(6)|
| def         | hq_db      | krneki_1 |id_aktiven          |              4 |10|10|tinyint(4) |
| def         | hq_db      | krneki_1 |cas_vnosa           |              5 | 9| 9|datetime   |
+-------------+------------+----------+--------------------+----------------+--+--+-----------+
5 rows in set (0.00 sec)

带十六进制的 krneki_2：

+-------------+------------+----------+-----------+----------------+--+--+-------------------------+
|table_catalog|table_schema|table_name|column_name|ordinal_position|cl|l | hex                     |      
+-------------+------------+----------+-----------+----------------+--+--+-------------------------+ 
| def         | hq_db      | krneki_2 |id         |              1 | 2| 2|6964                     |   
| def         | hq_db      | krneki_2 |id_naslov  |              2 |10|12|EFBBBF69645F6E61736C6F76 |   
| def         | hq_db      | krneki_2 |id_zaposlen|              3 |11|11|69645F7A61706F736C656E   |  
+-------------+------------+----------+-----------+----------------+--+--+-------------------------+   
3 rows in set (0.00 sec)  

带十六进制的 krneki_1：

+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+
|table_catalog|table_schema|table_name|column_name         |ordinal_position|cl| l|hex                                     |              
+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+
| def         | hq_db      | krneki_1 |id_naslovi_zaposleni|              1 |20|20|69645F6E61736C6F76695F7A61706F736C656E69|
| def         | hq_db      | krneki_1 |id_naslov           |              2 | 9| 9|69645F6E61736C6F76                      |
| def         | hq_db      | krneki_1 |id_zaposleni        |              3 |12|12|69645F7A61706F736C656E69                |
| def         | hq_db      | krneki_1 |id_aktiven          |              4 |10|10|69645F616B746976656E                    |
| def         | hq_db      | krneki_1 |cas_vnosa           |              5 | 9| 9|6361735F766E6F7361                      |
+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+
5 rows in set (0.00 sec)

如果只提供一个字母，Charindex 将不返回任何记录。如果参数与整个单词匹配，则显示记录。为什么？？？

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[filter_opombe] 
@opombe_par nchar(256)
AS
BEGIN    
    SELECT Id_opombe
    FROM opombe
    WHERE (CHARINDEX(@opomba_par, opomba,0) > 0)
END

提前感谢您的所有回复。