If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.
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 5.1.13, “MySQL Server Time Zone Support”.
Returns a representation of unix_timestamp as a DATETIME or VARCHAR value. The value returned is expressed using the session time zone. (Clients can set the session time zone as described in Section 5.1.13, “MySQL Server Time Zone Support”.) unix_timestamp is an internal timestamp value representing seconds since '1970-01-01 00:00:00' UTC, such as produced by the UNIX_TIMESTAMP() function.
unix_timestamp
When unix_timestamp is an integer, the fractional seconds precision of the DATETIME is 0.
When unix_timestamp is a decimal value, the fractional seconds precision of the DATETIME is the same as the precision of the decimal value, up to a maximum of 6.
When unix_timestamp is a floating point number, the fractional seconds precision of the datetime is 6.
format
If format is omitted, the value returned is a DATETIME.
If format is supplied, the value returned is a VARCHAR.
例子：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
-- 不指定 format 格式，返回 DATETIME 类型 SELECT FROM_UNIXTIME(1447430881); ->'2015-11-13 10:08:01'
If no date argument, it returns a Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC.
If with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.
The server interprets date as a value in the session time zone and converts it to an internal Unix timestamp value in UTC. (Clients can set the session time zone as described in Section 5.1.13, “MySQL Server Time Zone Support”.)
or a number in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format.
If the argument includes a time part, it may optionally include a fractional seconds part.
The return value is
an integer if no argument is given or the argument does not include a fractional seconds part,
or DECIMAL if an argument is given that includes a fractional seconds part.
When the date argument is a TIMESTAMP column, UNIX_TIMESTAMP() returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.
The valid range of argument values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0.
This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.
MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于作出更好的选择。
Smaller is usually better
更小的通常更好：
In general, try to use the smallest data type that can correctly store and represent your data. Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process.
Make sure you don’t underestimate the range of values you need to store, though, because increasing the data type range in multiple places in your schema can be a painful and time-consuming operation. If you’re in doubt as to which is the best data type to use, choose the smallest one that you don’t think you’ll exceed. (If the system is not very busy or doesn’t store much data, or if you’re at an early phase in the design process, you can change it easily later.)
更小的数据类型通常更快，因为占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。
Simple is good
简单就好：
Fewer CPU cycles are typically required to process operations on simpler data types. For example, integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Here are two examples: you should store dates and times in MySQL’s builtin types instead of as strings, and you should use integers for IP addresses. We discuss these topics further later.
简单数据类型的操作通常需要更少的 CPU 周期、索引性能更好。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使得字符比整型更复杂。
-- 测试表（注意使用 int UNSIGNED 类型存储（存储范围：0 ~ 2^32-1）） CREATE TABLE `t_iptable` ( `ip1` int(32) UNSIGNED NOT NULL COMMENT 'IPv4 地址', `ip2` varchar(15) NOT NULL COMMENT 'IPv4 地址' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 测试数据 INSERT INTO t_iptable(ip1, ip2) VALUES(INET_ATON('0.0.0.0'), '0.0.0.0'); INSERT INTO t_iptable(ip1, ip2) VALUES(INET_ATON('10.198.1.1'), '10.198.1.1'); INSERT INTO t_iptable(ip1, ip2) VALUES(INET_ATON('255.255.255.255'), '255.255.255.255');
-- 测试结果 -- INET_ATON() Return the numeric value of an IP address -- INET_NTOA() Return the IP address from a numeric value -- INET6_ATON() Return the numeric value of an IPv6 address -- INET6_NTOA() Return the IPv6 address from a numeric value SELECT INET_NTOA(ip1), ip2 FROM t_iptable; +-----------------+-----------------+ | INET_NTOA(ip1) | ip2 | +-----------------+-----------------+ |0.0.0.0|0.0.0.0| |10.198.1.1|10.198.1.1| |255.255.255.255|255.255.255.255| +-----------------+-----------------+
-- 测试数据 INSERT INTO t_hash(hash1, hash2) VALUES(0xE562F69EC36E625116376F376D991E41613E9BF3, 'E562F69EC36E625116376F376D991E41613E9BF3'); INSERT INTO t_hash(hash1, hash2) VALUES(X'E562F69EC36E625116376F376D991E41613E9BF3', 'E562F69EC36E625116376F376D991E41613E9BF3'); INSERT INTO t_hash(hash1, hash2) VALUES(UNHEX('E562F69EC36E625116376F376D991E41613E9BF3'), 'E562F69EC36E625116376F376D991E41613E9BF3');
-- 测试结果 -- BIT_LENGTH() Return length of a string in bits -- LENGTH() Return length of a string in bytes SELECT HEX(hash1), BIT_LENGTH(hash1), LENGTH(hash1), hash2, BIT_LENGTH(hash2), LENGTH(hash2) FROM t_hash; +------------------------------------------+-------------------+---------------+------------------------------------------+-------------------+---------------+ | HEX(hash1) | BIT_LENGTH(hash1) | LENGTH(hash1) | hash2 | BIT_LENGTH(hash2) | LENGTH(hash2) | +------------------------------------------+-------------------+---------------+------------------------------------------+-------------------+---------------+ | E562F69EC36E625116376F376D991E41613E9BF3 |160|20| E562F69EC36E625116376F376D991E41613E9BF3 |320|40| +------------------------------------------+-------------------+---------------+------------------------------------------+-------------------+---------------+
Avoid NULL if possible
避免使用 NULL：
A lot of tables include nullable columns even when the application does not need to store NULL (the absence of a value), merely because it’s the default. It’s usually best to specify columns as NOT NULL unless you intend to store NULL in them.
It’s harder for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.
The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make it a priority to find and change them on an existing schema unless you know they are causing problems. However, if you’re planning to index columns, avoid making them nullable if possible.
There are exceptions, of course. For example, it’s worth mentioning that InnoDB stores NULL with a single bit, so it can be pretty space-efficient for sparsely populated data. This doesn’t apply to MyISAM, though.
NULL 列使得 MySQL 索引、索引统计和值比较都更复杂。值可为 NULL 的列会使用更多的存储空间（例如当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节），在 MySQL 里也需要特殊处理。如果计划在列上建索引，应该尽量避免。
bytes for binary string types (BINARY(M)、VARBINARY(M))
characters for nonbinary string types (CHAR(M)、VARCHAR(M))
L represents the actual length in bytes of a given string value.
Binary Strings (Byte Strings)
Nonbinary Strings (Character Strings)
Storage Required
Fixed-length types
CHAR(M)
L = M × w bytes, 0 < M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
Fixed-length types
BINARY(M)
M bytes, 0 <= M <= 255
Variable-length types
VARBINARY(M)
VARCHAR(M)
L = M × w bytes + 1 bytes if column values require 0 − 255 bytes L = M × w bytes + 2 bytes if values may require more than 255 bytes 其有效最大字节长度取决于行大小限制（默认 65,535 Bytes，在所有列中共享） ，参考：《表列数量和行数限制》。
Variable-length types
TINYBLOB
TINYTEXT
L + 1 bytes, where L < 2^8 = 256 bytes
Variable-length types
BLOB
TEXT
L + 2 bytes, where L < 2^16 = 64 KB
Variable-length types
MEDIUMBLOB
MEDIUMTEXT
L + 3 bytes, where L < 2^24 = 16 MB
Variable-length types
LONGBLOB
LONGTEXT
L + 4 bytes, where L < 2^32 = 4 GB
variable-length types 变长类型需要额外的 1 到 4 个字节记录长度：
1 Byte = 8 bits 刚好可以记录 0~2^8-1 (255) bytes
2 Bytes = 16 bits 刚好可以记录 0~2^16-1 (65,535) bytes
3 Bytes = 24 bits 刚好可以记录 0~2^24 bytes
4 Bytes = 32 bits 刚好可以记录 0~2^32 bytes
Description
Binary Strings (Byte Strings)
They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.
Nonbinary Strings (Character Strings)
They have a character set other than binary, and values are sorted and compared based on the collation of the character set.
对于 Nonbinary Strings (Character Strings)，M 和 L 换算关系如下：
The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings. This means they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.
Hexadecimal literal values are written using X'val' or 0xval notation, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits and of any leading X does not matter. A leading 0x is case-sensitive and cannot be written as 0X.
Legal hexadecimal literals:
1 2 3 4 5 6
X'01AF' X'01af' x'01AF' x'01af' 0x01AF 0x01af
By default, a hexadecimal literal is a binary string, where each pair of hexadecimal digits represents a character:
1 2 3 4 5 6 7 8 9
-- BIT_LENGTH() Return length of a string in bits -- LENGTH() Return length of a string in bytes -- CHARSET() Returns the character set of the string argument. SELECT0x41, X'41', UNHEX('41'), BIT_LENGTH(0x41), LENGTH(0x41), CHARSET(0x41); +------+-------+-------------+------------------+--------------+---------------+ |0x41| X'41'| UNHEX('41') | BIT_LENGTH(0x41) | LENGTH(0x41) | CHARSET(0x41) | +------+-------+-------------+------------------+--------------+---------------+ | A | A | A |8|1|binary| +------+-------+-------------+------------------+--------------+---------------+
For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.
A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
…
TIMESTAMP 类型的范围如下：
时间戳
二进制字面量
时间
0
00000000 00000000 00000000 00000000
0000-00-00 00:00:00 UTC
1
00000000 00000000 00000000 00000001
1970-01-01 00:00:01 UTC
2^31-1, 2147483647
01111111 11111111 11111111 11111111
2038-01-19 03:14:07 UTC
TIMESTAMP 类型的时区处理：
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 5.1.13, “MySQL Server Time Zone Support”.
A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays DATETIME values in YYYY-MM-DD hh:mm:ss[.fraction] format, but permits assignment of values to DATETIME columns using either strings or numbers.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD hh:mm:ss[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.2.7, “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'), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 5.1.10, “Server SQL Modes”.
对于 MyISAM，Data_length 是数据文件的长度（以字节为单位）。 对于 InnoDB，Data_length 是聚簇索引 clustered index 大约分配的内存量（以字节为单位）。
Index_length
对于 MyISAM，Index_length 是索引文件的长度（以字节为单位）。 对于 InnoDB，Index_length 是非聚簇索引 non-clustered index 大约分配的内存量（以字节为单位）。
Auto_increment
下一个 AUTO_INCREMENT 值。
表结构
查看列名（三者等价）：
1 2 3
$ show columns from table_name [from db_name]; $ show columns from [db_name.]table_name; $ desc table_name; // 简写形式
索引
1
$ show index from table_name;
建表语句
1
$ show create table table_name;
查看用户权限
显示一个用户的权限，显示结果类似于 GRANT 命令：
1 2 3 4 5 6 7
$ show grants [for user_name@'192.168.0.%'];
+---------------------------------------------------------------------------------------------+ | Grants for user_name@192.168.0.% | +---------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE ON `db_name`.`table_name` TO 'user_name'@'192.168.0.%' | +---------------------------------------------------------------------------------------------+
// 查看当前 DB 进程 $ show processlist; $ show full processlist; +----------+-----------+--------------------+---------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----------+-----------+--------------------+---------+---------+------+-------+------------------+ | 33702451 | user_name | 192.168.0.200:49764 | db_name | Query | 0 | init | show processlist | +----------+-----------+--------------------+---------+---------+------+-------+------------------+
Dubbox 基于标准的 Java REST API——JAX-RS 2.0（Java API for RESTful Web Services 的简写），提供了接近透明的REST调用支持。由于完全兼容Java标准API，所以为dubbo开发的所有REST服务，未来脱离dubbo或者任何特定的REST底层实现一般也可以正常运行。
