注意与数据表关联之间的区别
A表
tid
subject
create table A(tid int(6) NOT NULL AUTO_INCREMENT UNIQUE,subject VARCHAR(20));
INSERT INTO A(subject) VALUES('a');
INSERT INTO A(subject) VALUES('b');
INSERT INTO A(subject) VALUES('c');
INSERT INTO A(subject) VALUES('d');
SELECT * FROM A; threads
+-----+---------+
| tid | subject |
+-----+---------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-----+---------+
B表
tid
name
create table B(tid int(6),name VARCHAR(20));
INSERT INTO B(tid,name) VALUES(1,'aa');
INSERT INTO B(tid,name) VALUES(1,'bb');
INSERT INTO B(tid,name) VALUES(1,'cc');
INSERT INTO B(tid,name) VALUES(1,'dd');
INSERT INTO B(tid,name) VALUES(2,'q');
INSERT INTO B(tid,name) VALUES(2,'w');
SELECT * FROM B; threadtags
+------+------+
| tid | name |
+------+------+
| 1 | aa |
| 1 | bb |
| 1 | cc |
| 1 | dd |
| 2 | q |
| 2 | w |
+------+------+
两种方案：
第一种：SELECT tid,subject FROM A WHERE tid not in(SELECT tid FROM B);
第二种：SELECT t.tid,t.subject FROM A t LEFT JOIN B tt ON t.tid=tt.tid where tt.tid is null;
+-----+---------+
| tid | subject |
+-----+---------+
| 3 | c |
| 4 | d |
+-----+---------+
国际日期变更线
create table C(tid int(6) NOT NULL AUTO_INCREMENT UNIQUE,name VARCHAR(20),dateline int(10));
mysql> describe C;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| tid | int(6) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| dateline | int(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
INSERT INTO C(name,dateline) VALUES('qinmi',UNIX_TIMESTAMP(now()));
SELECT * FROM C;
mysql> SELECT * FROM C;
+-----+-------+------------+
| tid | name | dateline |
+-----+-------+------------+
| 1 | qinmi | 2011 |
| 2 | qinmi | 1300244856 |
+-----+-------+------------+
2 rows in set (0.00 sec)
UNIX_TIMESTAMP(now()) UNIX时间戳，这样的表示形式虽然不直观，不过也少了在MSSQL中比较排序等的问题了，因为是整型值的比较，很简单。