select PurchaseInfoTable.PurchaseID from PurchaseInfoTable, appinfotable where PurchaseInfoTable.AppID=appinfotable.AppID and appinfotable.AppID = ‘2’;
appinfotable和PurchaseInfoTable中是有APPID为2的记录的,为什么这句话就是查不到这条记录?
但是给2加上一个单引号,就可以查到结果了。另外APPID都是varchar类型。
appinfotable和PurchaseInfoTable中是有APPID为2的记录的,为什么这句话就是查不到这条记录?
但是给2加上一个单引号,就可以查到结果了。另外APPID都是varchar类型。
Query OK, 0 rows affected (0.06 sec)mysql> insert into t6 values(2);
Query OK, 1 row affected (0.02 sec)mysql> select * from t6;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)mysql> select * from t6 where id = '2';
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)mysql> select * from t6 where id = 2;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)mysql>
+-------+
| 1+'1' |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
我发帖的时候说错了,是加引号查不出来,不加可以,但是appid又是varchar类型的,我知道字符串应该加引号,但是现在的情况是加了查不出来,不加反而可以。
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| AppID | varchar(36) | | PRI | | |
| AppName | varchar(40) | YES | | NULL | |
| Status | int(11) | YES | | NULL | |
| Description | varchar(200) | YES | | NULL | |
| UpLineTime | datetime | YES | | NULL | |
| DownLineTime | datetime | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+mysql> describe purchaseinfotable;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| PurchaseID | int(11) | | PRI | NULL | auto_increment |
| AppID | varchar(36) | YES | MUL | NULL | |
| UserID | varchar(16) | YES | MUL | NULL | |
| IsDel | int(11) | YES | | NULL | |
| DelTime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
appinfotable.AppID 的内容是 "002" 这样当然 不等于 "2" 但当 "002" = 2 时,这个式子会被MYSQL进行数据类型转换。"002" 字符串会被转换成数字 2 所以会相等
好滴,下回我问问题之前一定好好琢磨怎么样问的更准确:)
但是为什么你说我的appid会是“002”呢?
mysql> select * from appinfotable;
+-------+---------+--------+--------------------------------+-------------------
--+---------------------+
| AppID | AppName | Status | Description | UpLineTime
| DownLineTime |
+-------+---------+--------+--------------------------------+-------------------
--+---------------------+
| 2 | movie | 2 | 暴雪公司出品的一款即时战略游戏 | 2010-05-28 11:43:2
8 | 2010-05-28 12:04:54 |
| 10 | movie | 2 | 暴雪公司出品的一款即时战略游戏 | 2010-05-28 12:08:5
9 | 2010-06-03 10:55:09 |mysql> select * from purchaseinfotable;
+------------+-------+------------------+-------+---------------------+
| PurchaseID | AppID | UserID | IsDel | DelTime |
+------------+-------+------------------+-------+---------------------+
| 1 | 19 | 2010052811202714 | 1 | 2010-06-01 14:15:41 |
| 2 | 10 | 2010052811202714 | 1 | 2010-06-01 14:15:41 |
| 3 | 10 | 2010052811202714 | 1 | 2010-06-01 14:15:41 |
| 4 | 10 | 2010060114232078 | 0 | 1900-01-01 00:00:00 |
| 5 | 19 | 2010060114232078 | 0 | 1900-01-01 00:00:00 |
| 6 | 2 | 2010060114232078 | 0 | 1900-01-01 00:00:00 |
| 7 | 8 | 2010060114232078 | 1 | 2010-06-03 11:50:27 |
| 8 | 13 | 2010060114232078 | 0 | 1900-01-01 00:00:00 |
+------------+-------+------------------+-------+---------------------+
贴出以下结果,看看是不是你的存放的 '2 ' 后面有空格!select length(AppID ),AppID,concat('**',AppID,'**') from appinfotable;
select length(AppID ),AppID,concat('**',AppID,'**') from purchaseinfotable;
mysql> select length(AppID ),AppID,concat('**',AppID,'**') from appinfotable;
+----------------+-------+-------------------------+
| length(AppID ) | AppID | concat('**',AppID,'**') |
+----------------+-------+-------------------------+
| 1 | 1 | **1** |
| 2 | 10 | **10** |
| 2 | 11 | **11** |
| 2 | 12 | **12** |
| 2 | 13 | **13** |
| 2 | 14 | **14** |
| 2 | 15 | **15** |
| 2 | 16 | **16** |
| 2 | 17 | **17** |
| 2 | 18 | **18** |
| 2 | 19 | **19** |
| 1 | 2 | **2** |
| 2 | 20 | **20** |
| 2 | 21 | **21** |
| 1 | 3 | **3** |
| 1 | 4 | **4** |
| 1 | 5 | **5** |
| 1 | 6 | **6** |
| 1 | 7 | **7** |
| 1 | 8 | **8** |
| 1 | 9 | **9** |
+----------------+-------+-------------------------+
21 rows in set (0.05 sec)mysql> select length(AppID ),AppID,concat('**',AppID,'**') from purchaseinfotabl
e;
+----------------+-------+-------------------------+
| length(AppID ) | AppID | concat('**',AppID,'**') |
+----------------+-------+-------------------------+
| 2 | 10 | **10** |
| 2 | 10 | **10** |
| 2 | 10 | **10** |
| 2 | 13 | **13** |
| 2 | 19 | **19** |
| 2 | 19 | **19** |
| 1 | 2 | **2** |
| 1 | 8 | **8** |
+----------------+-------+-------------------------+
8 rows in set (0.00 sec)
提供你的 insert into 语句
提供你的 show variables like 'char%' 语句
提供你的 select version()
mysql> show create table appinfotable;
+--------------+--------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
-----------------------+
| Table | Create Table |
+--------------+--------------------------------------
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
-----------------------+
| appinfotable | CREATE TABLE `appinfotable` (
`AppID` varchar(36) NOT NULL default '',
`AppName` varchar(40) default NULL,
`Status` int(11) default NULL,
`Description` varchar(200) default NULL,
`UpLineTime` datetime default NULL,
`DownLineTime` datetime default NULL,
PRIMARY KEY (`AppID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |mysql> show variables like 'char%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_server | gbk |
| character_set_system | utf8 |
| character_set_database | gbk |
| character_set_client | gbk |
| character_set_connection | gbk |
| character-sets-dir | E:\SBS\SBS\Mysql5.0-sbs\share\charsets/ |
| character_set_results | gbk |
+--------------------------+-----------------------------------------+mysql> select version();
+-----------------------+
| version() |
+-----------------------+
| 5.0.0-alpha-max-debug |
+-----------------------+
1 row in set (0.00 sec)
提供你的 insert into 语句
提供你的 show variables like 'char%' 语句
提供你的 select version()=================
提供一下你的 insert 语句。以供别人测试!
mysql> insert into appinfotable values('22','movie',2,'aaaa','2010-05-28 11:43:2
8','2010-05-28 11:44:28');
Query OK, 1 row affected (0.05 sec)
mysql> insert into purchaseinfotable values(11,'22','2010052811202714',0,'2010-0
5-28 11:43:28');
Query OK, 1 row affected (0.03 sec)