英文:
How to SELECT from a comma spliced database string - SQL
问题
String name = null;
String keyword2 = null;
String author = "John";
PreparedStatement stmt3 = DBM.conn.prepareStatement("SELECT * FROM `timelines` LEFT JOIN `users` ON users.UserID = timelines.TimelineOwner WHERE " +
" `TimelineName` = COALESCE(NULLIF(?, ''), `TimelineName`) AND FIND_IN_SET(?, `Keywords`) > 0 AND `UserName` = COALESCE(NULLIF(?, ''), `UserName`);");
stmt3.setString(1, name);
stmt3.setString(2, keyword2);
stmt3.setString(3, author);
List<Timeline> list = DBM.getFromDB(stmt3, new Timeline());
在上面的代码中，我已经根据您的要求修改了查询语句，以便在关键字列比较时将参数与数据库中的单独单词进行比较。这里我使用了MySQL的FIND_IN_SET()函数来实现这一功能。
英文:
String name = null;
String keyword2 = null;
String author = "John";
PreparedStatement stmt3 = DBM.conn.prepareStatement("SELECT * FROM `timelines` LEFT JOIN `users` ON users.UserID = timelines.TimelineOwner WHERE " +
" `TimelineName` = COALESCE(NULLIF(?, ''), `TimelineName`) AND `Keywords` = COALESCE(NULLIF(?, ''), `Keywords`) and `UserName` = COALESCE(NULLIF(?, ''), `UserName`);") ;
stmt3.setString(1, name);
stmt3.setString(2, keyword2);
stmt3.setString(3, author);
List<Timeline> list = DBM.getFromDB(stmt3, new Timeline());
Here above is my search query for MySQL database and all is fine except there is one addition to it that I would like to add but I am lost in how to code it.
The thing is that the keywords are stored in a comma spliced String in the database and when I change the keyword parameter to, i.e "Rome", it should return that Timeline but it does not because the database String is Rome, Ceasar, Sun...... How can I add to the query so that when it comes to the Keywords column - it should compare the parameter to the words individually?
UPDATE: Maybe I can use FIND_IN_SET() and if the return from that is bigger than 0 then return its a match?
答案1
得分: 1
SELECT * FROM 'timelimes' WHERE 'keywords' LIKE %Rome%
英文:
You can use the SQL LIKE operator to search for a specific keyword in the comma separated string:
SELECT * FROM 'timelimes' WHERE 'keywords' LIKE %Rome%
答案2
得分: 1
你可以像这样使用FIND_IN_SET()：
SELECT *
FROM `timelines`
LEFT JOIN `users` ON users.UserID = timelines.TimelineOwner
WHERE `TimelineName` = COALESCE(NULLIF(?, ''), `TimelineName`)
AND FIND_IN_SET(COALESCE(NULLIF(?, ''), SUBSTRING_INDEX(`Keywords`, ',', 1), `Keywords`)
AND `UserName` = COALESCE(NULLIF(?, ''), `UserName`);
或者使用LIKE操作符：
SELECT *
FROM `timelines`
LEFT JOIN `users` ON users.UserID = timelines.TimelineOwner
WHERE `TimelineName` = COALESCE(NULLIF(?, ''), `TimelineName`)
AND CONCAT(',', `Keywords`, ',') LIKE CONCAT('%,', COALESCE(?, '%'), ',')
AND `UserName` = COALESCE(NULLIF(?, ''), `UserName`);
英文:
You can use FIND_IN_SET() like this:
SELECT *
FROM `timelines`
LEFT JOIN `users` ON users.UserID = timelines.TimelineOwner
WHERE `TimelineName` = COALESCE(NULLIF(?, ''), `TimelineName`)
AND FIND_IN_SET(COALESCE(NULLIF(?, ''), SUBSTRING_INDEX(`Keywords`, ',', 1), `Keywords`)
AND `UserName` = COALESCE(NULLIF(?, ''), `UserName`);
or with the operator LIKE:
SELECT *
FROM `timelines`
LEFT JOIN `users` ON users.UserID = timelines.TimelineOwner
WHERE `TimelineName` = COALESCE(NULLIF(?, ''), `TimelineName`)
AND CONCAT(',', `Keywords`, ',') LIKE CONCAT('%,', COALESCE(?, '%'), ',%')
AND `UserName` = COALESCE(NULLIF(?, ''), `UserName`);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论