Mysql Data Types

`INTEGER`, `INT`, `SMALLINT`, `TINYINT`,`MEDIUMINT`, `BIGINT`

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Type	Storage	Minimum Value	Maximum Value
	(Bytes)	(Signed/Unsigned)	Signed/Unsigned)
`TINYINT`	1	`-128`	`127`
		`0`	`255`
`SMALLINT`	2	`-32768`	`32767`
		`0`	`65535`
`MEDIUMINT`	3	`-8388608`	`8388607`
		`0`	`16777215`
`INT`	4	`-2147483648`	`2147483647`
		`0`	`4294967295`
`BIGINT`	8	`-9223372036854775808`	`9223372036854775807`
		`0`	`18446744073709551615`

`DECIMAL(65,30)`

`FOLAT(255,30)`

`DOUBLE(255,30)`

FLOAT:

要得到1位或2位精确小数的话，整数不能高于 32767

即：f<32767.99

因为 2E15=32768

所以最多只能正确处理0~32767的整数，

要得到3位 精确 小数的话，整数不能高于16383

即：f< 16383 .999

因为 2E14=16384

要得到4位 精确 小数，整数不能高于2047

即：f< 2047 .9999

而 2E11=2048

要得到5位 精确 小数，整数不能高于127

即：f< 127 .99999

而 2E7=128

要得到6位 精确 小数，整数不能高于15

即：f<15 .999999

而 2E4=16

要得到7位 精确 小数，整数不能高于0

即：f< 0 .9999999

而 2E0=0

要得到8位以上的 精确 小数，是不可能的

即：f不存在

笔者得到了一些数据，不过得不到它变化的规律，还请高人指点

笔者得出结论的过程：

我建了这个表：

create table q(

money float(15,3)

);

然后插入这个数据：

insert into q values(1234567.234);

再select一下读出来后发现，它显示的是：

1234567.250

然后插入这个数据：

insert into q values(12345672.34);

再select一下读出来后发现，它显示的是：

12345672.000

也就是说，有10数位与以上的话就会出现不准确的情况。

如果是这样的话：

create table q(
money float(10,3)
);

insert into q values(1234567.3);

得到的是：

1234567.250

insert into q values(1234567);

得到的是：

1234567.000

insert into q values(123456.7);

得到的是：

123456.703

insert into q values(12345.67);

得到的是：

12345.670

insert into q values(32768.990);

得到：32768.988

insert into q values(32767.990);

得到：32767.990

经过很多次的试验

要得到2位小数则整数部分最多不能超过32767。

表改为：

create table q(
money float(30,10)
);

对纯小数部分测试：

insert into q values(0.999);

得到：0.9990000129

insert into q values(0.9999);

得到：0.9998999834

insert into q values(0.99999);

得到：0.9999899864

insert into q values(327.99);

得到：327.9899902344

insert into q values(327.999);

得到：327.9989929199

表改为：

create table q(
money float(7,2)
);

insert into q values(327.99);

得到：327.99

insert into q values(327.9999);

得到:328.00

insert into q values(32767.9999);

得到：32768.00

insert into q values(32767.99);

得到： 32767.99

insert into q values(99999.99);

得到： 99999.99

表改为：

create table q(
money float(8,2)
);

insert into q values(999999.99);

得到：1000000.00

insert into q values(999998.99);

得到：999999.00

insert into q values(130999.99);

得到：130999.99

表改为：

create table q(
money float(9,3)
);

insert into q values(32768.990);

得到： 32768.988

insert into q values(32767.990);

得到：32767.990

insert into q values(16383.999);

得到：16383.999

要得到3位小数的话，整数不能高于16383

insert into q values(32767.90);

得到：32767.900

insert into q values(32768.90);

得到：32768.898

要得到1位小数的话，整数不能高于32767

The BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

To specify bit values, b'value' notation can be used. value is a binary value written using zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively. See Section 9.1.6, “Bit-Field Literals”.

If you assign a value to a BIT(M) column that is less than M bits long, the value is padded on the left with zeros. For example, assigning a value of b'101' to a BIT(6) column is, in effect, the same as assigning b'000101'.

Do not suggest to use TIMESTAMP .

The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and DATETIME range descriptions, “supported”means that although earlier values might work, there is no guarantee.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displaysDATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to'9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 10.6, “MySQL Server Time Zone Support”.

The TIMESTAMP data type offers automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME orTIMESTAMP columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

Be aware of certain properties of date value interpretation in MySQL:

MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the “:” delimiter, but is interpreted as the year'2010-11-12' if used in a date context. The value '10:45:15' is converted to '0000-00-00' because '45' is not a valid month.
The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enableALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.
MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:
- Year values in the range 00-69 are converted to 2000-2069.
- Year values in the range 70-99 are converted to 1970-1999.
See also Section 11.3.8, “Two-Digit Years in Dates”.

Note

The MySQL server can be run with the MAXDB SQL mode enabled. In this case, TIMESTAMP is identical withDATETIME. If this mode is enabled at the time that a table is created, TIMESTAMP columns are created asDATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and there is no automatic initialization or updating to the current date and time. See Section 5.1.7, “Server SQL Modes”.

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

MySQL recognizes TIME values in several formats, described in Section 9.1.3, “Date and Time Literals”. Some of these formats can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into TIME columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

Be careful about assigning abbreviated values to a TIME column. MySQL interprets abbreviated TIME values with colons as time of the day. That is, '11:12' means '11:12:00', not '00:11:12'. MySQL interprets abbreviated values without colons using the assumption that the two rightmost digits represent seconds (that is, as elapsed time rather than as time of day). For example, you might think of '1112' and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly, '12'and 12 are interpreted as '00:00:12'.

By default, values that lie outside the TIME range but are otherwise valid are clipped to the closest endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'. Invalid TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a valid TIME value, there is no way to tell, from a value of '00:00:00' stored in a table, whether the original value was specified as'00:00:00' or whether it was invalid.

For more restrictive treatment of invalid TIME values, enable strict SQL mode to cause errors to occur. SeeSection 5.1.7, “Server SQL Modes”.

The YEAR type is a 1-byte type used to represent year values. It can be declared as YEAR(4) or YEAR(2) to specify a display width of four or two characters. The default is four characters if no width is given.

Note

The YEAR(2) data type has certain issues that you should consider before choosing to use it. As of MySQL 5.5.27, YEAR(2) is deprecated. For more information, see Section 11.3.4, “YEAR(2) Limitations and Migrating toYEAR(4)”.

YEAR(4) and YEAR(2) differ in display format, but have the same range of values. For 4-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000. For 2-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).

You can specify input YEAR values in a variety of formats:

As a 4-digit string in the range '1901' to '2155'.
As a 4-digit number in the range 1901 to 2155.
As a 1- or 2-digit string in the range '0' to '99'. Values in the ranges '0' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
As a 1- or 2-digit number in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEARvalues in the ranges 2001 to 2069 and 1970 to 1999.

Inserting a numeric 0 has a different effect for YEAR(2) and YEAR(4). For YEAR(2), the result has a display value of 00 and an internal value of 2000. For YEAR(4), the result has a display value of 0000 and an internal value of0000. To specify zero for YEAR(4) and have it be interpreted as 2000, specify it as a string '0' or '00'.
As the result of a function that returns a value that is acceptable in a YEAR context, such as NOW().

Invalid YEAR values are converted to 0000.

so we should change year(2) to year(4)

数值类型存储需求

列类型	存储需求
TINYINT	1个字节
SMALLINT	2个字节
MEDIUMINT	3个字节
INT, INTEGER	4个字节
BIGINT	8个字节
FLOAT(p)	如果0 <= p <= 24为4个字节, 如果25 <= p <= 53为8个字节
FLOAT	4个字节
DOUBLE [PRECISION], item REAL	8个字节
DECIMAL(M,D), NUMERIC(M,D)	变长；参见下面的讨论
BIT(M)	大约(M+7)/8个字节

DECIMAL(和NUMERIC)的存储需求与具体版本有关：

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求：

剩余的	字节
位数	数目
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	4

日期和时间类型的存储需求

列类型	存储需求
DATE	3个字节
DATETIME	8个字节
TIMESTAMP	4个字节
TIME	3个字节
YEAR	1个字节

字符串类型的存储需求

列类型	存储需求
CHAR(M)	M个字节，0 <= M <= 255
VARCHAR(M)	L+1个字节，其中L <= M 且0 <= M <= 65535(参见下面的注释)
BINARY(M)	M个字节，0 <= M <= 255
VARBINARY(M)	L+1个字节，其中L <= M 且0 <= M <= 255
TINYBLOB, TINYTEXT	L+1个字节，其中L < 2⁸
BLOB, TEXT	L+2个字节，其中L < 2¹⁶
MEDIUMBLOB, MEDIUMTEXT	L+3个字节，其中L < 2²⁴
LONGBLOB, LONGTEXT	L+4个字节，其中L < 2³²
ENUM('value1','value2',...)	1或2个字节，取决于枚举值的个数(最多65,535个值)
SET('value1','value2',...)	1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度

Table 12.13. Date/Time Functions

Name	Description
`ADDDATE()`	Add time values (intervals) to a date value
`ADDTIME()`	Add time
`CONVERT_TZ()`	Convert from one timezone to another
`CURDATE()`	Return the current date
`CURRENT_DATE()`, `CURRENT_DATE`	Synonyms for CURDATE()
`CURRENT_TIME()`, `CURRENT_TIME`	Synonyms for CURTIME()
`CURRENT_TIMESTAMP()`,`CURRENT_TIMESTAMP`	Synonyms for NOW()
`CURTIME()`	Return the current time
`DATE_ADD()`	Add time values (intervals) to a date value
`DATE_FORMAT()`	Format date as specified
`DATE_SUB()`	Subtract a time value (interval) from a date
`DATE()`	Extract the date part of a date or datetime expression
`DATEDIFF()`	Subtract two dates
`DAY()`	Synonym for DAYOFMONTH()
`DAYNAME()`	Return the name of the weekday
`DAYOFMONTH()`	Return the day of the month (0-31)
`DAYOFWEEK()`	Return the weekday index of the argument
`DAYOFYEAR()`	Return the day of the year (1-366)
`EXTRACT()`	Extract part of a date
`FROM_DAYS()`	Convert a day number to a date
`FROM_UNIXTIME()`	Format UNIX timestamp as a date
`GET_FORMAT()`	Return a date format string
`HOUR()`	Extract the hour
`LAST_DAY`	Return the last day of the month for the argument
`LOCALTIME()`, `LOCALTIME`	Synonym for NOW()
`LOCALTIMESTAMP`,`LOCALTIMESTAMP()`	Synonym for NOW()
`MAKEDATE()`	Create a date from the year and day of year
`MAKETIME`	MAKETIME()
`MICROSECOND()`	Return the microseconds from argument
`MINUTE()`	Return the minute from the argument
`MONTH()`	Return the month from the date passed
`MONTHNAME()`	Return the name of the month
`NOW()`	Return the current date and time
`PERIOD_ADD()`	Add a period to a year-month
`PERIOD_DIFF()`	Return the number of months between periods
`QUARTER()`	Return the quarter from a date argument
`SEC_TO_TIME()`	Converts seconds to 'HH:MM:SS' format
`SECOND()`	Return the second (0-59)
`STR_TO_DATE()`	Convert a string to a date
`SUBDATE()`	A synonym for DATE_SUB() when invoked with three arguments
`SUBTIME()`	Subtract times
`SYSDATE()`	Return the time at which the function executes
`TIME_FORMAT()`	Format as time
`TIME_TO_SEC()`	Return the argument converted to seconds
`TIME()`	Extract the time portion of the expression passed
`TIMEDIFF()`	Subtract time
`TIMESTAMP()`	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
`TIMESTAMPADD()`	Add an interval to a datetime expression
`TIMESTAMPDIFF()`	Subtract an interval from a datetime expression
`TO_DAYS()`	Return the date argument converted to days
`TO_SECONDS()`	Return the date or datetime argument converted to seconds since Year 0
`UNIX_TIMESTAMP()`	Return a UNIX timestamp
`UTC_DATE()`	Return the current UTC date
`UTC_TIME()`	Return the current UTC time
`UTC_TIMESTAMP()`	Return the current UTC date and time
`WEEK()`	Return the week number
`WEEKDAY()`	Return the weekday index
`WEEKOFYEAR()`	Return the calendar week of the date (0-53)
`YEAR()`	Return the year
`YEARWEEK()`	Return the year and week

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

mysql> SELECT student_name,
    ->     GROUP_CONCAT(test_score)
    ->     FROM student
    ->     GROUP BY student_name;

Functions/Operators

Name	Description
`ABS()`	Return the absolute value
`ACOS()`	Return the arc cosine
`ADDDATE()`	Add time values (intervals) to a date value
`ADDTIME()`	Add time
`AES_DECRYPT()`	Decrypt using AES
`AES_ENCRYPT()`	Encrypt using AES
`AND`, `&&`	Logical AND
`ASCII()`	Return numeric value of left-most character
`ASIN()`	Return the arc sine
`=`	Assign a value (as part of a `SET` statement, or as part of the `SET` clause in an `UPDATE` statement)
`:=`	Assign a value
`ATAN2()`, `ATAN()`	Return the arc tangent of the two arguments
`ATAN()`	Return the arc tangent
`AVG()`	Return the average value of the argument
`BENCHMARK()`	Repeatedly execute an expression
`BETWEEN ... AND ...`	Check whether a value is within a range of values
`BIN()`	Return a string containing binary representation of a number
`BINARY`	Cast a string to a binary string
`BIT_AND()`	Return bitwise and
`BIT_COUNT()`	Return the number of bits that are set
`BIT_LENGTH()`	Return length of argument in bits
`BIT_OR()`	Return bitwise or
`BIT_XOR()`	Return bitwise xor
`&`	Bitwise AND
`~`	Invert bits
`\|`	Bitwise OR
`^`	Bitwise XOR
`CASE`	Case operator
`CAST()`	Cast a value as a certain type
`CEIL()`	Return the smallest integer value not less than the argument
`CEILING()`	Return the smallest integer value not less than the argument
`CHAR_LENGTH()`	Return number of characters in argument
`CHAR()`	Return the character for each integer passed
`CHARACTER_LENGTH()`	A synonym for CHAR_LENGTH()
`CHARSET()`	Return the character set of the argument
`COALESCE()`	Return the first non-NULL argument
`COERCIBILITY()`	Return the collation coercibility value of the string argument
`COLLATION()`	Return the collation of the string argument
`COMPRESS()`	Return result as a binary string
`CONCAT_WS()`	Return concatenate with separator
`CONCAT()`	Return concatenated string
`CONNECTION_ID()`	Return the connection ID (thread ID) for the connection
`CONV()`	Convert numbers between different number bases
`CONVERT_TZ()`	Convert from one timezone to another
`CONVERT()`	Cast a value as a certain type
`COS()`	Return the cosine
`COT()`	Return the cotangent
`COUNT(DISTINCT)`	Return the count of a number of different values
`COUNT()`	Return a count of the number of rows returned
`CRC32()`	Compute a cyclic redundancy check value
`CURDATE()`	Return the current date
`CURRENT_DATE()`, `CURRENT_DATE`	Synonyms for CURDATE()
`CURRENT_TIME()`, `CURRENT_TIME`	Synonyms for CURTIME()
`CURRENT_TIMESTAMP()`,`CURRENT_TIMESTAMP`	Synonyms for NOW()
`CURRENT_USER()`, `CURRENT_USER`	The authenticated user name and host name
`CURTIME()`	Return the current time
`DATABASE()`	Return the default (current) database name
`DATE_ADD()`	Add time values (intervals) to a date value
`DATE_FORMAT()`	Format date as specified
`DATE_SUB()`	Subtract a time value (interval) from a date
`DATE()`	Extract the date part of a date or datetime expression
`DATEDIFF()`	Subtract two dates
`DAY()`	Synonym for DAYOFMONTH()
`DAYNAME()`	Return the name of the weekday
`DAYOFMONTH()`	Return the day of the month (0-31)
`DAYOFWEEK()`	Return the weekday index of the argument
`DAYOFYEAR()`	Return the day of the year (1-366)
`DECODE()`	Decodes a string encrypted using ENCODE()
`DEFAULT()`	Return the default value for a table column
`DEGREES()`	Convert radians to degrees
`DES_DECRYPT()`	Decrypt a string
`DES_ENCRYPT()`	Encrypt a string
`DIV`	Integer division
`/`	Division operator
`ELT()`	Return string at index number
`ENCODE()`	Encode a string
`ENCRYPT()`	Encrypt a string
`<=>`	NULL-safe equal to operator
`=`	Equal operator
`EXP()`	Raise to the power of
`EXPORT_SET()`	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
`EXTRACT()`	Extract part of a date
`ExtractValue()`	Extracts a value from an XML string using XPath notation
`FIELD()`	Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()`	Return the index position of the first argument within the second argument
`FLOOR()`	Return the largest integer value not greater than the argument
`FORMAT()`	Return a number formatted to specified number of decimal places
`FOUND_ROWS()`	For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
`FROM_DAYS()`	Convert a day number to a date
`FROM_UNIXTIME()`	Format UNIX timestamp as a date
`GET_FORMAT()`	Return a date format string
`GET_LOCK()`	Get a named lock
`>=`	Greater than or equal operator
`>`	Greater than operator
`GREATEST()`	Return the largest argument
`GROUP_CONCAT()`	Return a concatenated string
`HEX()`	Return a hexadecimal representation of a decimal or string value
`HOUR()`	Extract the hour
`IF()`	If/else construct
`IFNULL()`	Null if/else construct
`IN()`	Check whether a value is within a set of values
`INET_ATON()`	Return the numeric value of an IP address
`INET_NTOA()`	Return the IP address from a numeric value
`INSERT()`	Insert a substring at the specified position up to the specified number of characters
`INSTR()`	Return the index of the first occurrence of substring
`INTERVAL()`	Return the index of the argument that is less than the first argument
`IS_FREE_LOCK()`	Checks whether the named lock is free
`IS NOT NULL`	NOT NULL value test
`IS NOT`	Test a value against a boolean
`IS NULL`	NULL value test
`IS_USED_LOCK()`	Checks whether the named lock is in use. Return connection identifier if true.
`IS`	Test a value against a boolean
`ISNULL()`	Test whether the argument is NULL
`LAST_DAY`	Return the last day of the month for the argument
`LAST_INSERT_ID()`	Value of the AUTOINCREMENT column for the last INSERT
`LCASE()`	Synonym for LOWER()
`LEAST()`	Return the smallest argument
`<<`	Left shift
`LEFT()`	Return the leftmost number of characters as specified
`LENGTH()`	Return the length of a string in bytes
`<=`	Less than or equal operator
`<`	Less than operator
`LIKE`	Simple pattern matching
`LN()`	Return the natural logarithm of the argument
`LOAD_FILE()`	Load the named file
`LOCALTIME()`, `LOCALTIME`	Synonym for NOW()
`LOCALTIMESTAMP`,`LOCALTIMESTAMP()`	Synonym for NOW()
`LOCATE()`	Return the position of the first occurrence of substring
`LOG10()`	Return the base-10 logarithm of the argument
`LOG2()`	Return the base-2 logarithm of the argument
`LOG()`	Return the natural logarithm of the first argument
`LOWER()`	Return the argument in lowercase
`LPAD()`	Return the string argument, left-padded with the specified string
`LTRIM()`	Remove leading spaces
`MAKE_SET()`	Return a set of comma-separated strings that have the corresponding bit in bits set
`MAKEDATE()`	Create a date from the year and day of year
`MAKETIME`	MAKETIME()
`MASTER_POS_WAIT()`	Block until the slave has read and applied all updates up to the specified position
`MATCH`	Perform full-text search
`MAX()`	Return the maximum value
`MD5()`	Calculate MD5 checksum
`MICROSECOND()`	Return the microseconds from argument
`MID()`	Return a substring starting from the specified position
`MIN()`	Return the minimum value
`-`	Minus operator
`MINUTE()`	Return the minute from the argument
`MOD()`	Return the remainder
`% or MOD`	Modulo operator
`MONTH()`	Return the month from the date passed
`MONTHNAME()`	Return the name of the month
`NAME_CONST()`	Causes the column to have the given name
`NOT BETWEEN ... AND ...`	Check whether a value is not within a range of values
`!=`, `<>`	Not equal operator
`NOT IN()`	Check whether a value is not within a set of values
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`NOT`, `!`	Negates value
`NOW()`	Return the current date and time
`NULLIF()`	Return NULL if expr1 = expr2
`OCT()`	Return a string containing octal representation of a number
`OCTET_LENGTH()`	A synonym for LENGTH()
`OLD_PASSWORD()`	Return the value of the pre-4.1 implementation of PASSWORD
`\|\|`, `OR`	Logical OR
`ORD()`	Return character code for leftmost character of the argument
`PASSWORD()`	Calculate and return a password string
`PERIOD_ADD()`	Add a period to a year-month
`PERIOD_DIFF()`	Return the number of months between periods
`PI()`	Return the value of pi
`+`	Addition operator
`POSITION()`	A synonym for LOCATE()
`POW()`	Return the argument raised to the specified power
`POWER()`	Return the argument raised to the specified power
`PROCEDURE ANALYSE()`	Analyze the results of a query
`QUARTER()`	Return the quarter from a date argument
`QUOTE()`	Escape the argument for use in an SQL statement
`RADIANS()`	Return argument converted to radians
`RAND()`	Return a random floating-point value
`REGEXP`	Pattern matching using regular expressions
`RELEASE_LOCK()`	Releases the named lock
`REPEAT()`	Repeat a string the specified number of times
`REPLACE()`	Replace occurrences of a specified string
`REVERSE()`	Reverse the characters in a string
`>>`	Right shift
`RIGHT()`	Return the specified rightmost number of characters
`RLIKE`	Synonym for REGEXP
`ROUND()`	Round the argument
`ROW_COUNT()`	The number of rows updated
`RPAD()`	Append string the specified number of times
`RTRIM()`	Remove trailing spaces
`SCHEMA()`	A synonym for DATABASE()
`SEC_TO_TIME()`	Converts seconds to 'HH:MM:SS' format
`SECOND()`	Return the second (0-59)
`SESSION_USER()`	Synonym for USER()
`SHA1()`, `SHA()`	Calculate an SHA-1 160-bit checksum
`SHA2()`	Calculate an SHA-2 checksum
`SIGN()`	Return the sign of the argument
`SIN()`	Return the sine of the argument
`SLEEP()`	Sleep for a number of seconds
`SOUNDEX()`	Return a soundex string
`SOUNDS LIKE`	Compare sounds
`SPACE()`	Return a string of the specified number of spaces
`SQRT()`	Return the square root of the argument
`STD()`	Return the population standard deviation
`STDDEV_POP()`	Return the population standard deviation
`STDDEV_SAMP()`	Return the sample standard deviation
`STDDEV()`	Return the population standard deviation
`STR_TO_DATE()`	Convert a string to a date
`STRCMP()`	Compare two strings
`SUBDATE()`	A synonym for DATE_SUB() when invoked with three arguments
`SUBSTR()`	Return the substring as specified
`SUBSTRING_INDEX()`	Return a substring from a string before the specified number of occurrences of the delimiter
`SUBSTRING()`	Return the substring as specified
`SUBTIME()`	Subtract times
`SUM()`	Return the sum
`SYSDATE()`	Return the time at which the function executes
`SYSTEM_USER()`	Synonym for USER()
`TAN()`	Return the tangent of the argument
`TIME_FORMAT()`	Format as time
`TIME_TO_SEC()`	Return the argument converted to seconds
`TIME()`	Extract the time portion of the expression passed
`TIMEDIFF()`	Subtract time
`*`	Multiplication operator
`TIMESTAMP()`	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
`TIMESTAMPADD()`	Add an interval to a datetime expression
`TIMESTAMPDIFF()`	Subtract an interval from a datetime expression
`TO_DAYS()`	Return the date argument converted to days
`TO_SECONDS()`	Return the date or datetime argument converted to seconds since Year 0
`TRIM()`	Remove leading and trailing spaces
`TRUNCATE()`	Truncate to specified number of decimal places
`UCASE()`	Synonym for UPPER()
`-`	Change the sign of the argument
`UNCOMPRESS()`	Uncompress a string compressed
`UNCOMPRESSED_LENGTH()`	Return the length of a string before compression
`UNHEX()`	Return a string containing hex representation of a number
`UNIX_TIMESTAMP()`	Return a UNIX timestamp
`UpdateXML()`	Return replaced XML fragment
`UPPER()`	Convert to uppercase
`USER()`	The user name and host name provided by the client
`UTC_DATE()`	Return the current UTC date
`UTC_TIME()`	Return the current UTC time
`UTC_TIMESTAMP()`	Return the current UTC date and time
`UUID_SHORT()`	Return an integer-valued universal identifier
`UUID()`	Return a Universal Unique Identifier (UUID)
`VALUES()`	Defines the values to be used during an INSERT
`VAR_POP()`	Return the population standard variance
`VAR_SAMP()`	Return the sample variance
`VARIANCE()`	Return the population standard variance
`VERSION()`	Returns a string that indicates the MySQL server version
`WEEK()`	Return the week number
`WEEKDAY()`	Return the weekday index
`WEEKOFYEAR()`	Return the calendar week of the date (0-53)
`XOR`	Logical XOR
`YEAR()`	Return the year
`YEARWEEK()`	Return the year and week

posted @ 2012-09-11 13:35 Jake.Xu 阅读(1359) 评论(0) 编辑收藏举报

刷新页面返回顶部

一亩三分地

Mysql Data Types

`INTEGER`, `INT`, `SMALLINT`, `TINYINT`,`MEDIUMINT`, `BIGINT`

`DECIMAL(65,30)`

`FOLAT(255,30)`

`DOUBLE(255,30)`

公告

一亩三分地

Mysql Data Types

INTEGER, INT, SMALLINT, TINYINT,MEDIUMINT, BIGINT

DECIMAL(65,30)

FOLAT(255,30)

DOUBLE(255,30)

公告

`INTEGER`, `INT`, `SMALLINT`, `TINYINT`,`MEDIUMINT`, `BIGINT`

`DECIMAL(65,30)`

`FOLAT(255,30)`

`DOUBLE(255,30)`