我已经使用Microsoft SQL Server工作多年了，但最近才开始在我的Web应用程序中使用MySQL，我渴望获取更多的知识。
为了继续探讨"隐藏功能"问题，我想了解MySQL的任何隐藏或方便的功能，这将有助于提高我对这个开源数据库的了解。
为了继续探讨"隐藏功能"问题，我想了解MySQL的任何隐藏或方便的功能，这将有助于提高我对这个开源数据库的了解。
20个回答
161
由于您设置了悬赏，我将分享我的心得...
总的来说，我今天优化的所有SQL都需要使用子查询。由于我来自Oracle数据库世界，我认为理所当然的事情在MySQL中并不适用。我的MySQL优化阅读使我得出结论，MySQL在优化查询方面落后于Oracle。
虽然对于大多数B2C应用程序而言，所需的简单查询可能适用于MySQL，但大多数智能报告所需的聚合报告类型的查询似乎需要进行相当多的规划和重新组织SQL查询，以指导MySQL更快地执行它们。
管理：
max_connections是并发连接数。默认值为100个连接（自5.0以来为151）-非常小。
注意：
连接占用内存，您的操作系统可能无法处理大量连接。
Linux/x86的MySQL二进制文件允许您拥有高达4096个并发连接，但自编译的二进制文件通常具有较少的限制。
将table_cache设置为与打开表和并发连接数相匹配的数量。观察open_tables值，如果它快速增长，则需要增加其大小。
注意：
前两个参数可能需要大量打开文件。20+max_connections+table_cache*2是您所需的良好估计。Linux上的MySQL有一个open_file_limit选项，请设置此限制。
如果您有复杂的查询，则sort_buffer_size和tmp_table_size可能非常重要。值将取决于查询复杂性和可用资源，但建议起始点分别为4Mb和32Mb。
注意：这些是“每个连接”的值，包括read_buffer_size、read_rnd_buffer_size等，这意味着每个连接可能需要这个值。因此，在设置这些参数时，请考虑负载和可用资源。例如，sort_buffer_size只有在MySQL需要进行排序时才会分配。注意：小心不要内存溢出。如果您建立了许多连接（即没有持久连接的网站），则可以通过将thread_cache_size设置为非零值来提高性能。 16是一个好的起点。增加该值，直到threads_created不会快速增长。
主键：
每个表只能有一个自动递增列，必须对其进行索引，并且不能具有默认值。
KEY通常是INDEX的同义词。当在列定义中给出时，也可以将关键字PRIMARY KEY指定为KEY。这是为了与其他数据库系统兼容而实现的。
主键是唯一索引，其中所有关键列都必须定义为NOT NULL。
如果主键或唯一索引仅由一个具有整数类型的列组成，则还可以在SELECT语句中将该列称为“_rowid”。
在MySQL中，主键的名称为PRIMARY。
目前，只有InnoDB（v5.1？）表支持外键。
通常，在创建表时创建所需的所有索引。任何声明为PRIMARY KEY、KEY、UNIQUE或INDEX的列都将被索引。
NULL表示“没有值”。要测试NULL，您不能使用算术比较运算符，例如=、<或<>。请改用IS NULL和IS NOT NULL运算符：
NO_AUTO_VALUE_ON_ZERO可以抑制0的自增，只有NULL才会生成下一个序列号。如果在表的AUTO_INCREMENT列中存储了0，则此模式可能很有用。（顺便说一句，存储0不是推荐的做法。）
要更改AUTO_INCREMENT计数器的值以用于新行：
ALTER TABLE mytable AUTO_INCREMENT = value;
或者 SET INSERT_ID = value;
除非另有说明，否则该值将以1000000开头，或指定如下:
...) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1
TIMESTAMPS:
TIMESTAMP列的值会从当前时区转换为UTC进行存储，从UTC转换为当前时区进行检索。
http://dev.mysql.com/doc/refman/5.1/en/timestamp.html 对于表中的一个TIMESTAMP列，您可以将当前时间戳分配为默认值和自动更新值。
使用其中一种类型在WHERE子句中时需要注意的一件事是，最好执行 WHERE datecolumn = FROM_UNIXTIME(1057941242) 而不是 WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242。 后者不会利用该列上的索引。
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
UNIX_TIMESTAMP()
FROM_UNIXTIME()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
如果您在MySQL中将日期时间转换为Unix时间戳：
然后再加上24小时：
然后再将其转换回日期时间，它就会神奇地失去一个小时！
这是发生的情况。当将Unix时间戳转换回日期时间时，考虑到时区，恰好在2006年10月28日至29日之间我们取消了夏令时并且失去了一个小时。
从MySQL 4.1.3开始，CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE()和FROM_UNIXTIME()函数返回连接的当前时区的值，该值作为time_zone系统变量的值可用。此外，UNIX_TIMESTAMP()假定其参数是当前时区的日期时间值。
当前时区设置不会影响由UTC_TIMESTAMP()等函数显示的值或DATE、TIME或DATETIME列中的值。
注意：ON UPDATE 仅当更新字段时DateTime才会更新。如果UPDATE导致没有更改字段，则DateTime不会更新！
另外，默认情况下，第一个TIMESTAMP总是自动更新，即使未指定。
在处理日期时，我几乎总是将其转换为儒略日期，因为数据计算只需要添加或减去整数，而秒数自午夜以来也是同样的原因。很少需要比秒更细的时间分辨率。
这两者都可以存储为4字节的整数，如果空间真的很紧，可以将它们组合成UNIX时间（自1970年1月1日时期以来的秒数）作为无符号整数，这将持续到大约2106年：
' 24小时有86400秒
' 有符号整数的最大值为2,147,483,647，可以容纳68年的秒数
' 无符号整数的最大值为4,294,967,295，可以容纳136年的秒数
二进制协议：
MySQL 4.1引入了一种二进制协议，允许非字符串数据值以本机格式发送和返回，而无需转换为和从字符串格式进行转换。（非常有用）
此外，mysql_real_query() 比 mysql_query() 更快，因为它不调用 strlen() 来操作语句字符串。
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html 二进制协议支持服务器端准备语句，并允许以本机格式传输数据值。在 MySQL 4.1 的早期版本中，二进制协议经历了相当多的修订。
您可以使用 IS_NUM() 宏测试字段是否具有数字类型。将类型值传递给 IS_NUM()，如果字段是数字，则计算结果为 TRUE:
需要注意的是，如果您对二进制数据进行转义并记住 MySQL 只需要转义反斜杠和引号字符，就可以在常规查询中发送二进制数据。因此，这是插入较短的二进制字符串（例如加密/盐值密码）的一种非常简单的方法。
主服务器：
http://www.experts-exchange.com/Database/MySQL/Q_22967482.html
http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2
授予复制从服务器权限 . 给slave_user用户，密码为'slave_password'
#Master Binary Logging Config STATEMENT causes replication
to be statement-based - default
log-bin=Mike
binlog-format=STATEMENT
server-id=1
max_binlog_size = 10M
expire_logs_days = 120
#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2
二进制日志文件必须读取：
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
你可以使用RESET MASTER语句删除所有二进制日志文件，或者使用PURGE MASTER命令删除其中的一部分。--result-file = binlog.txt TrustedFriend-bin.000030
规范化：
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
UDF函数
http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx
http://souptonuts.sourceforge.net/readme_mysql.htm
数据类型：
http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2
http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/
注意：在混合了CHAR和VARCHAR的表中，MySQL会将CHAR更改为VARCHAR。RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT，PRIMARY KEY (RecNum)
MySQL始终按照标准SQL和ISO 8601规范以年为先表示日期。
杂项：
关闭一些MySQL功能将导致更小的数据文件和更快的访问速度。例如：
--datadir将指定数据目录
--skip-innodb将关闭inno选项并节省10-20M
更多内容请参见http://dev.mysql.com/tech-resources/articles/mysql-c-api.html 下载第7章-免费
InnoDB是事务性的，但它带来了性能开销。我发现MyISAM表足以满足90％的项目需求。非事务安全表（MyISAM）具有自己的优点，所有这些优点都是因为：
没有事务开销：
速度更快
较低的磁盘空间要求
执行更新所需的内存较少
每个MyISAM表在磁盘上以三个文件存储。这些文件的名称以表名开头，并具有指示文件类型的扩展名。.frm文件存储表格式。数据文件具有.MYD（MYData）扩展名。索引文件具有.MYI（MYIndex）扩展名。
这些文件可以完整地复制到存储位置，而无需使用耗时的MySQL管理员备份功能（还有恢复）。技巧是复制这些文件，然后删除表。当您把文件放回MySQL时，它将识别它们并更新跟踪表。如果您必须备份/恢复，还原备份或从现有转储文件导入可能需要很长时间，具体取决于每个表上的索引和主键数量。您可以通过以下方式修改原始转储文件来大大加快此过程:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
为了显著提高重新加载的速度，在转储文件开头添加SQL命令"SET AUTOCOMMIT = 0;"，并在结尾添加"COMMIT;"命令。
默认情况下，自动提交是开启的，这意味着每个插入命令都会被视为单独的事务并在下一个事务开始之前写入磁盘。如果您不添加这些命令，将大型数据库重新加载到InnoDB中可能需要很多小时...
MySQL表中行的最大大小为65,535字节。
MySQL 5.0.3及以上版本中VARCHAR的有效最大长度=最大行大小（65,535字节）。
存储VARCHAR值时不进行填充。在存储和检索值时保留尾随空格，符合标准SQL。
在MySQL中，CHAR和VARCHAR值的比较不考虑尾随空格。
如果整个记录是固定大小，则使用CHAR只会加快访问速度。也就是说，如果您使用任何可变大小对象，那么您可能会使它们全部变成可变大小。在包含VARCHAR的表中使用CHAR不会增加速度。
MySQL 5.0.3将VARCHAR限制从255个字符提高到65535个字符。
只有MyISAM表支持全文搜索。
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
BLOB列没有字符集，排序和比较基于列值中字节的数值。
如果未启用严格的SQL模式并将值分配给BLOB或TEXT列，超过列的最大长度，则该值将被截断以适应，并生成警告。
有用的命令：
检查严格模式： SELECT @@global.sql_mode;
关闭严格模式：
SET @@global.sql_mode= '';
SET @@global.sql_mode='MYSQL40'
或删除： sql-mode="STRICT_TRANS_TABLES,...
显示mytable中的列：
SHOW COLUMNS FROM mytable
从mytable中选择namecount的最大值作为虚拟列并按虚拟列排序：
SELECT max(namecount) AS virtualcolumn FROM mytable ORDER BY virtualcolumn
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id()
使用get获取当前线程中最后插入行的PK，max(pkcolname)获取整个表中的最后一个PK。
注意：如果表为空，则max(pkcolname)返回1。mysql_insert_id()将本地MySQL C API函数mysql_insert_id()的返回类型转换为long（在PHP中命名为int）。
如果您的AUTO_INCREMENT列具有BIGINT列类型，则mysql_insert_id()返回的值将不正确。而是在SQL查询中使用内部MySQL SQL函数LAST_INSERT_ID()。
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
请注意，当您尝试将数据插入表格时，如果出现以下错误：
Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’
使用类似的东西。
INSERT INTO table (this, that) VALUES ($this, $that)
这是因为你在尝试插入表格中的值时没有使用任何撇号。所以你应该将你的代码更改为：
INSERT INTO table (this, that) VALUES ('$this', '$that')
提醒：``用于定义MySQL字段、数据库或表，而不是值 ;)
查询期间与服务器的连接丢失：
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.1/en/show-variables.html
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
http://dev.mysql.com/doc/refman/5.1/en/error-log.html
调整查询
http://www.artfulsoftware.com/infotree/queries.php?&bw=1313
足以赚取奖金了，我想...许多小时和许多项目的成果，使用了一个伟大的免费数据库。我主要在Windows平台上开发应用程序数据服务器，大多数使用MySQL。我需要整理的最糟糕的混乱是 终极MySQL遗留数据库噩梦。这需要一系列应用程序来处理表格，将其转化为有用的内容，使用了这里提到的许多技巧。如果您发现这非常有帮助，请通过投票来表达感谢。还可以查看我的其他文章和白皮书：www.coastrd.com。- Mike Trader
21
一个用于查找当前缓存中有哪些表的命令：
mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | a | 3 | 0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)
(来自MySQL性能博客)
- Eran Galperin
15
查找当前谁在做什么的命令：
mysql> show processlist;
show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL |
| 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)
你可以使用以下方法终止一个进程：
mysql>kill 5
- CMS
1
5如果您不想查询被截断，还需执行SHOW FULL PROCESSLIST命令。 - Greg
11
我特别喜欢MySQL内置的支持 inet_ntoa() 和 inet_aton()，使得在表格中处理IP地址非常简单（至少对于IPv4地址而言）！
- Alnitak
2
2PostgreSQL有一个非常好的inet类型，可以很好地处理IPv4和IPv6 :-) - mat
我以前也喜欢它们，但是根本不用它们更好。Postgres加一分。 - Kornel
11
我喜欢使用 on duplicate key（也称作upsert、merge）来处理各种懒惰创建的计数器：
insert into occurances(word,count) values('foo',1),('bar',1)
on duplicate key cnt=cnt+1
您可以在一个查询中插入多行数据，并立即处理每个行的重复索引。
- Kornel
10
再次强调，这并不是真正的隐藏功能，但非常方便：
功能
轻松获取DDL：
SHOW CREATE TABLE CountryLanguage
输出结果：
CountryLanguage | CREATE TABLE countrylanguage (
CountryCode char(3) NOT NULL DEFAULT '',
Language char(30) NOT NULL DEFAULT '',
IsOfficial enum('T','F') NOT NULL DEFAULT 'F',
Percentage float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (CountryCode,Language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
示例 1：简单
SELECT CountryCode
, GROUP_CONCAT(Language) AS List
FROM CountryLanguage
GROUP BY CountryCode
输出：
+-------------+------------------------------------+
| CountryCode | List |
+-------------+------------------------------------+
| ABW | Dutch,English,Papiamento,Spanish |
. ... . ... .
| ZWE | English,Ndebele,Nyanja,Shona |
+-------------+------------------------------------+
示例2：多个参数
SELECT CountryCode
, GROUP_CONCAT(
Language
, IF(IsOfficial='T', ' (Official)', '')
) AS List
FROM CountryLanguage
GROUP BY CountryCode
输出：
+-------------+---------------------------------------------+
| CountryCode | List |
+-------------+---------------------------------------------+
| ABW | Dutch (Official),English,Papiamento,Spanish |
. ... . ... .
| ZWE | English (Official),Ndebele,Nyanja,Shona |
+-------------+---------------------------------------------+
示例3：使用自定义分隔符
SELECT CountryCode
, GROUP_CONCAT(Language SEPARATOR ' and ') AS List
FROM CountryLanguage
GROUP BY CountryCode
输出：
+-------------+----------------------------------------------+
| CountryCode | List |
+-------------+----------------------------------------------+
| ABW | Dutch and English and Papiamento and Spanish |
. ... . ... .
| ZWE | English and Ndebele and Nyanja and Shona |
+-------------+----------------------------------------------+
示例4：控制列表元素的顺序
SELECT CountryCode
, GROUP_CONCAT(
Language
ORDER BY CASE IsOfficial WHEN 'T' THEN 1 ELSE 2 END DESC
, Language
) AS List
FROM CountryLanguage
GROUP BY CountryCode
输出：
+-------------+------------------------------------+
| CountryCode | List |
+-------------+------------------------------------+
| ABW | English,Papiamento,Spanish,Dutch, |
. ... . ... .
| ZWE | Ndebele,Nyanja,Shona,English |
+-------------+------------------------------------+
特性: COUNT(DISTINCT ) 支持多个表达式
您可以在COUNT(DISTINCT ...)表达式中使用多个表达式来计算组合的数量。
SELECT COUNT(DISTINCT CountryCode, Language) FROM CountryLanguage
特性 / 注意事项: GROUP BY列表中不需要包含非聚合表达式
大多数关系型数据库管理系统都强制要求SQL92兼容的GROUP BY语法，要求在SELECT列表中的所有非聚合表达式都必须出现在GROUP BY中。在这些关系型数据库管理系统中，以下语句是正确的：
SELECT Country.Code, Country.Continent, COUNT(CountryLanguage.Language)
FROM CountryLanguage
INNER JOIN Country
ON CountryLanguage.CountryCode = Country.Code
GROUP BY Country.Code
因为SELECT列表包含未出现在GROUP BY列表中的非聚合列Country.Continent，所以不合法。在这些RDBMS中，您必须修改GROUP BY列表以使其符合要求。
GROUP BY Country.Code, Country.Continent
或者你必须将一些无意义的聚合添加到Country.Continent中，例如
SELECT Country.Code, MAX(Country.Continent), COUNT(CountryLanguage.Language)
现在，问题是，从逻辑上讲，并没有要求对 Country.Continent 进行聚合。因为 Country.Code 是 Country 表的主键。Country.Continent 也是来自 Country 表的一列，因此根据定义是函数依赖于主键 Country.Code。因此，每个不同的 Country.Code 必须存在一个值 Country.Continent。如果你意识到这一点，那么你就会意识到聚合它没有意义（只有一个值，对吗），也不需要按它分组（因为已经按主键分组了，不会使结果更加唯一）。
无论如何，MySQL 允许你在 SELECT 列表中包含未进行聚合的列，而不需要将它们添加到 GROUP BY 子句中。
但是，这种方法的问题在于 MySQL 不会保护你免受使用未聚合列的影响。所以，像这样的查询：
SELECT Country.Code, COUNT(CountryLanguage.Language), CountryLanguage.Percentage
FROM CountryLanguage
INNER JOIN Country
ON CountryLanguage.CountryCode = Country.Code
GROUP BY Country.Code
这段代码会被执行而不会报错，但是CountryLanguage.Percentage列将包含无意义的内容（也就是说，在所有语言的百分比中，一个可用的百分比值将被随机选择或至少超出您的控制范围）。
参见：揭穿分组错误的神话
-
1
在Oracle中，我最不喜欢的功能之一就是允许未在group by中声明的列。如果你习惯了Oracle，这是一个很大的陷阱 - 它只是让你运行查询，结果看起来没问题，但后来你会意识到它并没有做你想要的事情。 - mbafford
7
客户端中的“pager”命令
如果您的结果中有10,000行，并且想要查看它们（这假设“less”和“tee”命令可用，通常在Linux下是这种情况；在Windows下可能会有所不同。）
pager less
select lots_of_stuff FROM tbl WHERE clause_which_matches_10k_rows;
您将在“less”文件查看器中获得它们，因此您可以很好地翻页、搜索等。
另外
pager tee myfile.txt
select a_few_things FROM tbl WHERE i_want_to_save_output_to_a_file;
可以方便地写入文件。
- MarkR
1
不幸的是，在 Windows 下，即使存在 "less" 和 "tee"，分页器选项本身也不受支持。无论如何都不容易。 - Berry Tsakala
6
以下可能会引起您的兴趣：
<query>\G -- \G in the CLI instead of the ; will show one column per row
explain <query>; -- this will show the execution plan for the query
- SorinV
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 110 HTML的隐藏功能
- 29 VBA的隐藏功能是什么？
- 72 Bash的隐藏功能
- 232 Windows批处理文件的隐藏功能
- 174 PHP的隐藏功能是什么？
- 121 VB.NET的隐藏功能是什么？
- 121 Android开发的隐藏功能？
- 8 MSBuild的隐藏功能
- 109 Xcode 4的隐藏功能
- 79 PostgreSQL的隐藏功能
SET SESSION sql_mode='ANSI';命令禁用一些陷阱。 - Kornel