V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Sukizen
V2EX  ›  MySQL

SELECT时候,如何处理某字段空值?

  •  
  •   Sukizen · 2012-12-07 03:23:34 +08:00 · 6497 次点击
    这是一个创建于 4381 天前的主题,其中的信息可能已经有所发展或是发生改变。
    以下是我的php代码:

    $sql = "SELECT author,tags,subject,tid,authorid,dateline,fid FROM threads ORDER BY dateline DESC LIMIT 10"


    如果tags是空值,则跳过,该怎么改呢?
    10 条回复    1970-01-01 08:00:00 +08:00
    regmach
        1
    regmach  
       2012-12-07 04:15:22 +08:00   ❤️ 1
    $sql = "SELECT author,tags,subject,tid,authorid,dateline,fid FROM threads WHERE tags IS NOT NULL ORDER BY dateline DESC LIMIT 10";
    // 字段 IS NOT NULL
    Sukizen
        2
    Sukizen  
    OP
       2012-12-07 10:59:18 +08:00
    @regmach 感谢!
    因为我的空值是 ''
    所以我根据你的找出了

    $sql = "SELECT author,tags,subject,tid,authorid,dateline,fid FROM threads WHERE tags !='' ORDER BY dateline DESC LIMIT 10"

    并且是有效的。
    fangzhzh
        3
    fangzhzh  
       2012-12-07 11:21:22 +08:00   ❤️ 1
    鉴于你可能有以下需求:
    有些记录虽然某些字段为空NULL,但是还要取出来,但是为了防护,要给这些空NULL字段一个默认值
    select author,coalesce(tags,'defualt') ,subject,tid,authorid,dateline,fid FROM threads

    mysql 中 help coallesce
    liuyao729
        4
    liuyao729  
       2012-12-07 15:54:38 +08:00
    @fangzhzh 在mysql中用这函数会比在php中判断效率高吗?
    fangzhzh
        5
    fangzhzh  
       2012-12-07 16:33:18 +08:00
    木有用过php

    但是感觉要高吧,毕竟亲儿子,原生的啊
    Sukizen
        6
    Sukizen  
    OP
       2012-12-09 14:57:29 +08:00
    @fangzhzh 请教一下:
    如果我只想得出 authorid 是 1 的数据,并且按照时间排序。下面语句是否正确:
    $sql = "SELECT author,tags,subject,tid,authorid,dateline,fid FROM threads WHERE authorid ='1' ORDER BY dateline DESC LIMIT 10"
    fangzhzh
        7
    fangzhzh  
       2012-12-10 07:53:58 +08:00   ❤️ 1
    @Sukizen 你的sql意思是: 取这些字段, 条件:authorid是1. 排序: dateline 倒序, 限制:取前10条。
    1 如果authorid是数字类型的话,可以去掉引号, 直接 authorid = 1
    2 看你的需求是木有前十条这个的,这个前10条是不是写错了。
    Sukizen
        8
    Sukizen  
    OP
       2012-12-10 12:17:52 +08:00
    @fangzhzh 再次感谢。
    新手学语言,还是测试一次最能吸收。
    昨天测试过了,DESC LIMIT 10 我知道什么意思,我复制代码提问的时候,忘记去掉了,谢谢你的提醒。

    我因为要或者不同类型的数据,导致一个页面查询很多次,不知道有什么好的方法让查询次数少一点呢。
    以下是我在对discuz作修改的时候加入的代码,请赐教。
    我的想法:
    1、获取新主题,但是过滤上级论坛分类 ( fup not in (6) ),过滤匿名发表的主题(author !='')。
    2、获取最近回复的主题,同样论坛分类 6 ( fup not in (6) ),过滤匿名发表的主题(author !='')。
    3、由于上级论坛分类6是一个原创专区,所以我单独获取这个分区的最新帖子。

    //newthread start
    $colorarray = array('', 'red', 'orange', 'yellow', 'green', 'cyan', 'blue', 'purple', 'gray');
    $hack_cut_str = 50;
    $hack_cut_strauthor = 9;
    $new_post_threadlist = array();
    $nthread = array();
    $query = $db->query("SELECT t.*, f.name FROM {$tablepre}threads t, {$tablepre}forums f WHERE f.fup not in (6) AND t.fid<>'$fid' AND f.fid=t.fid AND f.fid not in (0) AND t.displayorder not in (-1,-2) AND author !='' ORDER BY t.dateline DESC LIMIT 0, 20");
    while($nthread = $db->fetch_array($query)) {
    $nthread['forumname'] = strip_tags($nthread['name']);
    $nthread['view_subject'] = cutstr($nthread['subject'],$hack_cut_str);
    $nthread['view_author'] = cutstr($nthread['author'],$hack_cut_strauthor);
    $nthread['date'] = dgmdate("$dateformat $timeformat", $nthread['dateline'] + $timeoffset * 3600);
    $nthread['lastreplytime']= dgmdate("$dateformat $timeformat", $nthread[lastpost] + ($timeoffset * 3600));
    if($nthread['highlight']) {
    $string = sprintf('%02d', $nthread['highlight']);
    $stylestr = sprintf('%03b', $string[0]);
    $nthread['highlight'] = 'style="';
    $nthread['highlight'] .= $stylestr[0] ? 'font-weight: bold;' : '';
    $nthread['highlight'] .= $stylestr[1] ? 'font-style: italic;' : '';
    $nthread['highlight'] .= $stylestr[2] ? 'text-decoration: underline;' : '';
    $nthread['highlight'] .= $string[1] ? 'color: '.$colorarray[$string[1]] : '';
    $nthread['highlight'] .= '"';
    } else {
    $nthread['highlight'] = '';
    }
    $new_post_threadlist[] = $nthread;
    }
    //newthread end

    //newreply start
    $new_reply_threadlist = array();
    $rthread = array();
    $query = $db->query("SELECT t.*, f.name FROM {$tablepre}threads t, {$tablepre}forums f WHERE f.fup not in (6) AND t.fid<>'$fid' AND f.fid=t.fid AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 AND f.fid not in (0) AND t.displayorder not in (-1,-2) AND author !='' ORDER BY t.lastpost DESC LIMIT 0, 20");
    while($rthread = $db->fetch_array($query)) {
    $rthread['forumname'] = ereg_replace('<[^>]*>','',$rthread['name']);
    $rthread['view_subject'] = cutstr($rthread['subject'],$hack_cut_str);
    $rthread['view_lastposter'] = cutstr($rthread['lastposter'],$hack_cut_strauthor);
    $rthread['date']= dgmdate("$dateformat $timeformat", $rthread['dateline'] + $timeoffset * 3600);
    $rthread['lastreplytime']= dgmdate("$dateformat $timeformat", $rthread[lastpost] + ($timeoffset * 3600));
    if($rthread['highlight']) {
    $string = sprintf('%02d', $rthread['highlight']);
    $stylestr = sprintf('%03b', $string[0]);
    $rthread['highlight'] = 'style="';
    $rthread['highlight'] .= $stylestr[0] ? 'font-weight: bold;' : '';
    $rthread['highlight'] .= $stylestr[1] ? 'font-style: italic;' : '';
    $rthread['highlight'] .= $stylestr[2] ? 'text-decoration: underline;' : '';
    $rthread['highlight'] .= $string[1] ? 'color: '.$colorarray[$string[1]] : '';
    $rthread['highlight'] .= '"';
    } else {
    $rthread['highlight'] = '';
    }
    $new_reply_threadlist[] = $rthread;
    }
    //newreply end

    //myspace start
    $my_post_threadlist = array();
    $mythread = array();
    $query = $db->query("SELECT t.*, f.name FROM {$tablepre}threads t, {$tablepre}forums f WHERE f.fup in (6) AND t.fid<>'$fid' AND f.fid=t.fid AND f.fid not in (0) AND t.displayorder not in (-1,-2) AND author !='' ORDER BY t.dateline DESC LIMIT 0, 20");
    while($mythread = $db->fetch_array($query)) {
    $mythread['forumname'] = strip_tags($mythread['name']);
    $mythread['view_subject'] = cutstr($mythread['subject'],$hack_cut_str);
    $mythread['view_author'] = cutstr($mythread['author'],$hack_cut_strauthor);
    $mythread['date'] = dgmdate("$dateformat $timeformat", $mythread['dateline'] + $timeoffset * 3600);
    $mythread['lastreplytime']= dgmdate("$dateformat $timeformat", $mythread[lastpost] + ($timeoffset * 3600));
    $my_post_threadlist[] = $mythread;
    }
    //myspace end
    enj0y
        9
    enj0y  
       2012-12-10 12:29:10 +08:00
    在设置表时设置上默认值。在读表时容错,这样才相对安全一些。楼上的方案都不错
    zxy
        10
    zxy  
       2012-12-10 13:29:53 +08:00
    我在图书馆扫书的时候看到过一本程序员的SQL金典,你可以弄本当参考书
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5767 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 03:27 · PVG 11:27 · LAX 19:27 · JFK 22:27
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.