dongnuo6310
2014-08-26 11:17
浏览 83
已采纳

如何从mysql中删除具有给定id的记录

Now i have the table like

--------------------------
merge_id  |  merge_combo |
--------------------------
A1        |   25,9       |
A2        |   25,21      |
A3        |   2,5,15     |
A4        |   2,9,5      |
A5        |   12,19,2    |
A6        |   2,1        |
--------------------------

for example now if i pass the value 9, then it should check the merge_combo and delete the rows which are having 9 (A1,A4 this two having 9)

图片转代码服务由CSDN问答提供 功能建议

现在我的表格如

  ----  ---------------------- 
merge_id |  merge_combo | 
 -------------------------- 
A1 |  25,9 | 
A2 |  25,21 | 
A3 |  2,5,15 | 
A4 |  2,9,5 | 
A5 |  12,19,2 | 
A6 |  2,1 | 
 -------------------------- 
   
 
 

for 例如,如果我传递值9,那么它应该检查merge_combo并删除具有9的行(A1,A4这两个有9)

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • douciwang6819 2014-08-26 11:24
    已采纳

    You can do this with find_in_set():

    delete from tbl
        where find_in_set(9, merge_combo) > 0;
    

    The bigger issue is that you are storing numbers in a string for a list. SQL has this great data structure for storing lists -- it is called a table. In this case, you want a junction table, with one row per merge_id and combo item.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douchi0028 2014-08-26 11:24

    You can use the following qry

    DELETE FROM tbl WHERE merge_combo LIKE '%,9,%' OR merge_combo LIKE '9,%' OR merge_combo LIKE '%,9' OR merge_combo = '9';
    
    评论
    解决 无用
    打赏 举报
  • dougaopu7938 2014-08-26 11:24

    You may use Find_In_Set

    delete from your_table where FIND_IN_SET(@val, merge_combo)
    

    FIND_IN_SET(str,strlist)

    Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

    评论
    解决 无用
    打赏 举报
  • doudieyou5209 2014-08-26 11:26

    Use Below query

    DELETE FROM tbl WHERE FIND_IN_SET("9",merge_combo)
    
    评论
    解决 无用
    打赏 举报
  • doubi1624 2014-08-26 11:26
    DELETE from your_table where FIND_IN_SET(9, merge_combo) or merge_combo IN(9)
    
    评论
    解决 无用
    打赏 举报
  • dongyimeng3764 2014-08-26 11:27

    You should not create a complex SQL query with LIKE.If you have a bad db design your app will be bad too.

    Instead normalize your database that comes up even against the 1NF. Try to break merge_combo to merge_comboA and merge_comboB and merge_comboC if you know that you will have to untill 3 combo values or for the best implementation create a separate table with composite keys (this way you will avoid Null values)

    PS : use the queries proposed only if you can't change your database (e.g. an academic exercise to test your knoweledge on queries)

    评论
    解决 无用
    打赏 举报
  • douxiajia6104 2014-08-26 11:33

    Connection:

    $mysqli = new mysqli ('localhost', 'admin_user', 'Paradise1191', 'admin_db');
    
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s
    ", mysqli_connect_error());
        exit();
    }
    
    $mysqli->set_charset("utf8");
    


    Select & Delete:

    $var = "Your Result";
    $array = array();
    
    $query = "SELECT * FROM `TABLE` WHERE 1";
    
    if ($result = $mysqli->query($query)) {
    
    while ($row = $result->fetch_array())
    {
        $this_id = $row['merge_id'];
        $var1 = $row['merge_combo'];
        $array = explode(",",$var1 );
        if (in_array($var, $people)) {
    
        $query = "DELETE FROM `TABLE` WHERE `merge_id` = '$this_id'";
        $mysqli->query($query);        
    
        }
    
    }
    $result->close();
    }
    
    评论
    解决 无用
    打赏 举报
  • douhuang2282 2014-08-26 11:58

    you should try this following query:

    DELETE FROM merge WHERE merge_combo LIKE '% 9 %';
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题