mysql - Substring 返回数字值而不是 varchar

Question

$query = mysql_query(SELECT * FROM test WHERE SUBSTRING(date,0,4)="1392") //for example 1392
echo query['name'];

我想得到4个第一个字符并检查它是否等于1392。 但它的返回 1054 我的列名类型是 varchar 我对此一无所知。

Answer 1

MySQL从1开始计数字符，而不是像php那样从0开始计数。尝试一下

SUBSTRING(date,1,4)="1392"

或者如果你想要年份

YEAR(date)="1392"

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring

编辑测试示例

CREATE TABLE babak (name VARCHAR(20) NOT NULL, date VARCHAR(10) NOT NULL);
INSERT INTO babak SELECT 'test', '1391/11/11';
INSERT INTO babak SELECT 'correct', '1932/11/11';

SELECT * FROM babak WHERE YEAR(date) = 1932;
+---------+------------+
| name    | date       |
+---------+------------+
| correct | 1932/11/11 |
+---------+------------+

SELECT * FROM babak WHERE SUBSTRING(date, 1, 4) = 1932;
+---------+------------+
| name    | date       |
+---------+------------+
| correct | 1932/11/11 |
+---------+------------+