MySQL MySQL的隐藏功能

Question

提问by GateKiller

I've been working with Microsoft SQL Serverwith many years now but have only just recently started to use MySQLwith my web applications, and I'm hungry for knowledge.

我已经使用Microsoft SQL Server很多年了，但最近才开始在我的 Web 应用程序中使用MySQL，我渴望获得知识。

To continue with the long line of "hidden feature" questions, I would like to know any hidden or handy features of MySQL which will hopefully improve my knowledge of this open source database.

继续“隐藏功能”问题的长线，我想知道 MySQL 的任何隐藏或方便的功能，希望能提高我对这个开源数据库的了解。

Answer 1

回答by Mike Trader

Since you put up a bounty, I'll share my hard won secrets...

既然你悬赏了，我就分享我来之不易的秘密……

In general, all the SQLs I tuned today required using sub-queries. Having come from Oracle database world, things I took for granted weren't working the same with MySQL. And my reading on MySQL tuning makes me conclude that MySQL is behind Oracle in terms of optimizing queries.

通常，我今天调整的所有 SQL 都需要使用子查询。来自 Oracle 数据库世界，我认为理所当然的事情与 MySQL 不同。我对 MySQL 调优的阅读使我得出结论，MySQL 在优化查询方面落后于 Oracle。

While the simple queries required for most B2C applications may work well for MySQL, most of the aggregate reporting type of queries needed for Intelligence Reporting seems to require a fair bit of planning and re-organizing the SQL queries to guide MySQL to execute them faster.

虽然大多数 B2C 应用程序所需的简单查询可能适用于 MySQL，但智能报告所需的大多数聚合报告类型的查询似乎需要相当多的规划和重新组织 SQL 查询，以指导 MySQL 更快地执行它们。

Administration:

行政：

max_connectionsis the number of concurrent connections. The default value is 100 connections (151 since 5.0) - very small.

max_connections是并发连接数。默认值为 100 个连接（从 5.0 开始为 151 个） - 非常小。

Note:

笔记：

connections take memory and your OS might not be able to handle a lot of connections.

连接占用内存，您的操作系统可能无法处理大量连接。

MySQL binaries for Linux/x86 allow you to have up to 4096 concurrent connections, but self compiled binaries often have less of a limit.

适用于 Linux/x86 的 MySQL 二进制文件允许您拥有多达 4096 个并发连接，但自编译的二进制文件通常没有限制。

Set table_cache to match the number of your open tables and concurrent connections. Watch the open_tables value and if it is growing quickly you will need to increase its size.

设置 table_cache 以匹配您打开的表和并发连接的数量。观察 open_tables 值，如果它快速增长，您将需要增加其大小。

Note:

笔记：

The 2 previous parameters may require a lot of open files. 20+max_connections+table_cache*2 is a good estimate for what you need. MySQL on Linux has an open_file_limit option, set this limit.

前面的 2 个参数可能需要大量打开的文件。20+max_connections+table_cache*2 是您需要的一个很好的估计。Linux 上的 MySQL 有一个 open_file_limit 选项，设置这个限制。

If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points.

如果您有复杂的查询 sort_buffer_size 和 tmp_table_size 可能非常重要。值将取决于查询复杂性和可用资源，但分别是 4Mb 和 32Mb 是建议的起点。

Note: These are "per connection" values, among read_buffer_size, read_rnd_buffer_size and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL needs to do a sort. Note: be careful not to run out of memory.

注意：这些是“每个连接”的值，包括 read_buffer_size、read_rnd_buffer_size 和其他一些值，这意味着每个连接可能需要这个值。因此，在设置这些参数时，请考虑您的负载和可用资源。例如 sort_buffer_size 仅在 MySQL 需要进行排序时分配。注意：注意不要耗尽内存。

If you have many connects established (i.e. a web site without persistent connections) you might improve performance by setting thread_cache_size to a non-zero value. 16 is good value to start with. Increase the value until your threads_created do not grow very quickly.

如果您建立了许多连接（即没有持久连接的网站），您可以通过将 thread_cache_size 设置为非零值来提高性能。16 是一个很好的开始。增加该值，直到您的threads_created 增长不是很快。

PRIMARY KEY:

首要的关键：

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value

每个表只能有一个 AUTO_INCREMENT 列，它必须被索引，并且不能有 DEFAULT 值

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

KEY 通常是 INDEX 的同义词。在列定义中给出时，键属性 PRIMARY KEY 也可以仅指定为 KEY。这是为了与其他数据库系统兼容而实施的。

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL

PRIMARY KEY 是唯一索引，其中所有键列都必须定义为 NOT NULL

If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as "_rowid" in SELECT statements.

如果 PRIMARY KEY 或 UNIQUE 索引仅包含一个整数类型的列，您还可以在 SELECT 语句中将该列称为“_rowid”。

In MySQL, the name of a PRIMARY KEY is PRIMARY

在 MySQL 中，PRIMARY KEY 的名称是 PRIMARY

Currently, only InnoDB (v5.1?) tables support foreign keys.

目前，只有 InnoDB (v5.1?) 表支持外键。

Usually, you create all the indexes you need when you are creating tables. Any column declared as PRIMARY KEY, KEY, UNIQUE, or INDEX will be indexed.

通常，您在创建表时会创建所需的所有索引。任何声明为 PRIMARY KEY、KEY、UNIQUE 或 INDEX 的列都将被索引。

NULL means "not having a value". To test for NULL, you cannotuse the arithmetic comparison operators such as =, <, or <>. Use the IS NULL and IS NOT NULL operators instead:

NULL 表示“没有值”。要测试 NULL，您不能使用算术比较运算符，例如 =、< 或 <>。请改用 IS NULL 和 IS NOT NULL 运算符：

NO_AUTO_VALUE_ON_ZERO suppresses auto increment for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.)

NO_AUTO_VALUE_ON_ZERO 禁止自动递增 0，以便只有 NULL 生成下一个序列号。如果 0 已存储在表的 AUTO_INCREMENT 列中，则此模式很有用。（顺便说一下，存储 0 不是推荐的做法。）

To change the value of the AUTO_INCREMENT counter to be used for new rows:

要更改用于新行的 AUTO_INCREMENT 计数器的值：

ALTER TABLE mytable AUTO_INCREMENT = value;

or SET INSERT_ID = value;

或 SET INSERT_ID = 值；

Unless otherwise specified, the value will begin with: 1000000 or specify it thus:

除非另有说明，否则该值将以：1000000 开头或这样指定：

...) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

TIMESTAMPS:

时间戳：

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

TIMESTAMP 列的值从当前时区转换为 UTC 进行存储，并从 UTC 转换为当前时区进行检索。

http://dev.mysql.com/doc/refman/5.1/en/timestamp.htmlFor one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value.

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html对于表中的一个 TIMESTAMP 列，您可以将当前时间戳指定为默认值和自动更新值。

one thing to watch out for when using one of these types in a WHERE clause, it is best to do WHERE datecolumn = FROM_UNIXTIME(1057941242) and not WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242. doing the latter won't take advantage of an index on that column.

在 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()

if you convert a datetime to unix timestamp in MySQL:
And then add 24 hours to it:
And then convert it back to a datetime it magically loses an hour!

如果您将日期时间转换为 MySQL 中的 unix 时间戳：
然后向其添加 24 小时：
然后将其转换回日期时间，它神奇地失去了一个小时！

Here's what's happening. When converting the unix timestamp back to a datetime the timezone is taken into consideration and it just so happens that between the 28th and the 29th of October 2006 we went off daylight savings time and lost an hour.

这就是正在发生的事情。当将 unix 时间戳转换回日期时间时，时区被考虑在内，恰好在 2006 年 10 月 28 日和 29 日之间，我们取消了夏令时并损失了一个小时。

Beginning with MySQL 4.1.3, the CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values in the connection's current time zone, which is available as the value of the time_zone system variable. In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the current time zone.

从 MySQL 4.1.3 开始，CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE() 和 FROM_UNIXTIME() 函数返回连接当前时区的值，该值可用作 time_zone 系统变量的值。此外，UNIX_TIMESTAMP() 假定其参数是当前时区中的日期时间值。

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns.

当前时区设置不影响 UTC_TIMESTAMP() 等函数显示的值或 DATE、TIME 或 DATETIME 列中的值。

NOTE: ON UPDATE ONLYupdates the DateTime if a field is changed If an UPDATE results in no fields being changed then the DateTime is NOT updated!

注意：ON UPDATE仅在字段更改时更新 DateTime 如果 UPDATE 导致未更改任何字段，则 DateTime 不会更新！

Addtionally, the First TIMESTAMP is always AUTOUPDATE by default even if not specified

此外，即使未指定，默认情况下第一个 TIMESTAMP 始终为 AUTOUPDATE

When working with Dates, I almost always convet to Julian Date becuase Data math is then a simple matter of adding or subtracing integers, and Seconds since Midnight for the same reason. It is rare I need time resoultion of finer granularity than seconds.

在处理日期时，我几乎总是转而使用 Julian Date，因为数据数学是添加或减去整数的简单问题，出于同样的原因，从午夜开始计算秒数。我很少需要比秒更细粒度的时间分辨率。

Both these can be stored as a 4 byte integer, and if space is really tight can be combined into UNIX time (seconds since the epoch 1/1/1970) as an unsigned integer which will be good till around 2106 as:

这两个都可以存储为 4 字节整数，如果空间非常紧张，可以将 UNIX 时间（自 1970 年 1 月 1 日以来的秒数）作为无符号整数组合到 2106 年左右，如下所示：

' secs in 24Hrs = 86400

' 24 小时内的秒数 = 86400

' Signed Integer max val = 2,147,483,647 - can hold 68 years of Seconds

' 有符号整数最大值 val = 2,147,483,647 - 可以保存 68 年的秒数

' Unsigned Integer max val = 4,294,967,295 - can hold 136 years of Seconds

' 无符号整数最大值 val = 4,294,967,295 - 可以保存 136 年的秒数

Binary Protocol:

二进制协议：

MySQL 4.1 introduced a binary protocol that allows non-string data values to be sent and returned in native format without conversion to and from string format. (Very usefull)

MySQL 4.1 引入了一种二进制协议，允许以本机格式发送和返回非字符串数据值，而无需在字符串格式之间进行转换。（非常好用）

Aside, mysql_real_query() is faster than mysql_query() because it does not call strlen() to operate on the statement string.

此外，mysql_real_query() 比 mysql_query() 更快，因为它不调用 strlen() 来操作语句字符串。

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.htmlThe binary protocol supports server-side prepared statements and allows transmission of data values in native format. The binary protocol underwent quite a bit of revision during the earlier releases of MySQL 4.1.

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html二进制协议支持服务器端准备好的语句并允许以本机格式传输数据值。在 MySQL 4.1 的早期版本中，二进制协议经历了相当多的修订。

You can use the IS_NUM() macro to test whether a field has a numeric type. Pass the type value to IS_NUM() and it evaluates to TRUE if the field is numeric:

您可以使用 IS_NUM() 宏来测试字段是否具有数字类型。将类型值传递给 IS_NUM()，如果字段是数字，它的计算结果为 TRUE：

One thing to note is that binary data CANbe sent inside a regular query if you escape it and remember MySQL requires onlythat backslash and the quote character be escaped. So that is a really easy way to INSERT shorter binary strings like encrypted/Salted passwords for example.

有一点要注意的是，二进制数据CAN常规查询中被发送，如果你逃避它，记住MySQL的要求仅是反斜线和引号字符转义。因此，这是插入较短的二进制字符串（例如加密/加盐密码）的一种非常简单的方法。

Master Server:

主服务器：

http://www.experts-exchange.com/Database/MySQL/Q_22967482.html

http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

GRANT REPLICATION SLAVE ON .to slave_user IDENTIFIED BY 'slave_password'

授予复制从属。到 slave_user IDENTIFIED BY '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

Binary Log File must read:

二进制日志文件必须为：

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

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE MASTER

您可以使用 RESET MASTER 语句删除所有二进制日志文件，或者使用 PURGE MASTER 删除其中的一部分

--result-file=binlog.txt TrustedFriend-bin.000030

Normalization:

正常化：

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

UDF functions

UDF 函数

http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx

http://souptonuts.sourceforge.net/readme_mysql.htm

DataTypes:

数据类型：

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/

One thing to note is that on a mixed table with both CHAR and VARCHAR, mySQL will change the CHAR's to VARCHAR's

需要注意的一件事是，在同时包含 CHAR 和 VARCHAR 的混合表上，mySQL 会将 CHAR 更改为 VARCHAR

RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (RecNum)

MySQL always represents dates with the year first, in accordance with the standard SQL and ISO 8601 specifications

根据标准 SQL 和 ISO 8601 规范，MySQL 始终以年份表示日期

Misc:

杂项：

Turing off some MySQl functionality will result in smaller data files and faster access. For example:

关闭一些 MySQl 功能将导致更小的数据文件和更快的访问。例如：

--datadir will specify the data directory and

--datadir 将指定数据目录和

--skip-innodb will turn off the inno option and save you 10-20M

--skip-innodb 将关闭 inno 选项并为您节省 10-20M

More here http://dev.mysql.com/tech-resources/articles/mysql-c-api.html

更多在这里 http://dev.mysql.com/tech-resources/articles/mysql-c-api.html

Download Chapter 7 - Free

下载第 7 章 - 免费

InnoDB is transactional but there is a performance overhead that comes with it. I have found MyISAM tables to be sufficient for 90% of my projects. Non-transaction-safe tables (MyISAM) have several advantages of their own, all of which occur because:

InnoDB 是事务性的，但随之而来的是性能开销。我发现 MyISAM 表足以满足我 90% 的项目。非事务安全表 (MyISAM) 有自己的几个优点，所有这些优点都是因为：

there is no transaction overhead:

没有交易开销：

Much faster

快多了

Lower disk space requirements

较低的磁盘空间要求

Less memory required to perform updates

执行更新所需的内存更少

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

每个 MyISAM 表都存储在磁盘上的三个文件中。这些文件的名称以表名开头，并具有指示文件类型的扩展名。.frm 文件存储表格式。数据文件具有 .MYD (MYData) 扩展名。索引文件具有 .MYI (MYIndex) 扩展名。

These Files canbe copied to a storage location intact without using the MySQL Administrators Backup feature which is time consuming (so is the Restore)

这些文件可以原封不动地复制到存储位置，而无需使用耗时的 MySQL 管理员备份功能（还原也是如此）

The trick is make a copy of these files then DROP the table. When you put the files back MySQl will recognize them and update the table tracking.

诀窍是制作这些文件的副本，然后删除表。当您将文件放回时，MySQl 将识别它们并更新表跟踪。

If you must Backup/Restore,

如果您必须备份/恢复，

Restoring a backup, or importing from an existing dump file can takes a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

恢复备份或从现有转储文件导入可能需要很长时间，具体取决于每个表上的索引和主键的数量。您可以通过使用以下内容修改原始转储文件来显着加快此过程：

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

To vastly increase the speed of the reload, add the SQL command SET AUTOCOMMIT = 0; at the beginning of the dump file, and add the COMMIT; command to the end.

要大大提高重新加载的速度，请添加 SQL 命令 SET AUTOCOMMIT = 0; 在转储文件的开头，并添加 COMMIT；命令到最后。

By default, autocommit is on, meaning that each and every insert command in the dump file will be treated as a separate transaction and written to disk before the next one is started. If you don't add these commands, reloading a large database into InnoDB can take many hours...

默认情况下，自动提交是打开的，这意味着转储文件中的每个插入命令都将被视为一个单独的事务并在下一个开始之前写入磁盘。如果不添加这些命令，将大型数据库重新加载到 InnoDB 中可能需要花费数小时...

The maximum size of a row in a MySQL table is 65,535 bytes

MySQL 表中一行的最大大小为 65,535 字节

The effective maximum length of a VARCHAR in MySQL 5.0.3 and on = maximum row size (65,535 bytes)

MySQL 5.0.3 和 on = 最大行大小（65,535 字节）中 VARCHAR 的有效最大长度

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

VARCHAR 值在存储时不会被填充。根据标准 SQL，在存储和检索值时保留尾随空格。

CHAR and VARCHAR values in MySQL are compared without regard to trailing spaces.

MySQL 中的 CHAR 和 VARCHAR 值在不考虑尾随空格的情况下进行比较。

Using CHAR will only speed up your access if the whole record is fixed size. That is, if you use any variable size object, you might as well make all of them variable size. You gain no speed by using a CHAR in a table that also contains a VARCHAR.

如果整个记录是固定大小，则使用 CHAR 只会加快您的访问速度。也就是说，如果您使用任何可变大小的对象，您最好将它们全部设置为可变大小。在还包含 VARCHAR 的表中使用 CHAR 不会提高速度。

The VARCHAR limit of 255 characters was raised to 65535 characters as of MySQL 5.0.3

从 MySQL 5.0.3 开始，255 个字符的 VARCHAR 限制提高到 65535 个字符

Full-text searches are supported for MyISAM tables only.

仅 MyISAM 表支持全文搜索。

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

BLOB columns have no character set, and sorting and comparison are based on the numeric values of the bytes in column values

BLOB 列没有字符集，排序和比较都是根据列值中字节的数值

If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated.

如果未启用严格的 SQL 模式，并且您为 BLOB 或 TEXT 列分配的值超过该列的最大长度，则该值将被截断以适应并生成警告。

Useful Commands:

有用的命令：

check strict mode: SELECT @@global.sql_mode;

检查严格模式：SELECT @@global.sql_mode;

turn off strict mode:

关闭严格模式：

SET @@global.sql_mode= '';

SET @@global.sql_mode='';

SET @@global.sql_mode='MYSQL40'

or remove: sql-mode="STRICT_TRANS_TABLES,...

或删除： sql-mode="STRICT_TRANS_TABLES,...

SHOW COLUMNS FROM mytable

显示列来自 mytable

SELECT max(namecount) AS virtualcolumnFROM 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-idlast_insert_id()

gets you the PK of the last row inserted in the current thread max(pkcolname) gets you last PK overall.

为您提供插入当前线程的最后一行的 PK max(pkcolname) 为您提供最后的 PK。

Note: if the table is empty max(pkcolname) returns 1 mysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP).

注意：如果表为空 max(pkcolname) 返回 1 mysql_insert_id() 将原生 MySQL C API 函数 mysql_insert_id() 的返回类型转换为 long 类型（在 PHP 中命名为 int）。

If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

如果您的 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

Just a note that when you're trying to insert data into a table and you get the error:

请注意，当您尝试将数据插入表中时，您会收到错误消息：

Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list'

using something like

使用类似的东西

INSERT INTO table (this, that) VALUES ($this, $that)

it's because you've not got any apostrophes around the values you're trying to stick into the table. So you should change your code to:

这是因为你没有在你试图坚持的价值观周围有任何撇号。因此，您应该将代码更改为：

INSERT INTO table (this, that) VALUES ('$this', '$that')

reminder that `` are used to define MySQL fields, databases, or tables, not values ;)

提醒 `` 用于定义 MySQL 字段、数据库或表，而不是值；)

Lost connection to server during query:

查询期间与服务器的连接丢失：

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

Tuning Queries

调优查询

http://www.artfulsoftware.com/infotree/queries.php?&bw=1313

Well that should be enough to earn the bonus I would think... The fruits of many hours and many projects with a great freedatabase. I develop application data servers on windows platforms mostly with MySQL. The worst mess I had to straighten out was

嗯，这应该足以获得我认为的奖金......许多小时和许多项目的成果以及一个伟大的免费数据库。我主要使用 MySQL 在 Windows 平台上开发应用程序数据服务器。我必须解决的最糟糕的事情是

The ultimate MySQL legacy database nightmare

MySQL 遗留数据库的终极噩梦

This required a series of appplications to process the tables into something usefull using many of the tricks mentioned here.

这需要一系列应用程序使用这里提到的许多技巧将表格处理成有用的东西。

If you found this astoundingly helpfull, express your thanks by voting it up.

如果您发现这非常有帮助，请通过投票来表达您的感谢。

Also check out my other articles and white papers at: www.coastrd.com

还可以在以下网址查看我的其他文章和白皮书：www.coastrd.com

Answer 2

回答by mat

One of the not so hidden feature of MySQL is that it's not really good at being SQL compliant, well, not bugs really, but, more gotchas... :-)

其中的MySQL没有这么隐蔽的特点就是，它不是在为SQL兼容的真的很好，很好，不是真正的错误，但是，更多的陷阱... :-)

Answer 3

回答by Eran Galperin

A command to find out what tables are currently in the cache:

找出当前缓存中的表的命令：

mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | a     |      3 |           0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)

(From MySQL performance blog)

（来自MySQL 性能博客）

Answer 4

回答by CMS

A command to find out who is doing what:

找出谁在做什么的命令：

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)

And you can kill a process with:

您可以使用以下命令终止进程：

mysql>kill 5

Answer 5

回答by Alnitak

I particularly like MySQL's built-in support for inet_ntoa()and inet_aton(). It makes handling of IP addresses in tables very straightforward (at least so long as they're only IPv4 addresses!)

我特别喜欢 MySQL 对inet_ntoa()和的内置支持inet_aton()。它使处理表中的 IP 地址变得非常简单（至少只要它们只是 IPv4 地址！）

Answer 6

回答by Kornel

I love on duplicate key(AKA upsert, merge) for all kinds of counters created lazily:

我喜欢on duplicate key（AKA upsert，merge）各种懒惰创建的计数器：

insert into occurances(word,count) values('foo',1),('bar',1) 
  on duplicate key cnt=cnt+1

You can insert many rows in one query, and immediately handle duplicate index for each of the rows.

您可以在一个查询中插入多行，并立即处理每一行的重复索引。

Answer 7

回答by Kornel

Again - not really hidden features, but really handy:

再次 - 不是真正隐藏的功能，但非常方便：

Feature

特征

Easily grab DDL:

轻松抢DDL：

SHOW CREATE TABLE CountryLanguage

output:

输出：

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

Feature: GROUP_CONCAT() aggregate functionCreates a concatenated string of its arguments per detail, and aggregates by concatenating those per group.

功能：GROUP_CONCAT() 聚合函数为每个细节创建一个连接的参数字符串，并通过连接每个组的参数进行聚合。

Example 1: simple

示例 1：简单

SELECT   CountryCode
,        GROUP_CONCAT(Language) AS List
FROM     CountryLanguage
GROUP BY CountryCode

Output:

输出：

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | Dutch,English,Papiamento,Spanish   |
. ...         . ...                                .
| ZWE         | English,Ndebele,Nyanja,Shona       |
+-------------+------------------------------------+

Example 2: multiple arguments

示例 2：多个参数

SELECT   CountryCode
,        GROUP_CONCAT(
             Language
,            IF(IsOfficial='T', ' (Official)', '')
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Output:

输出：

+-------------+---------------------------------------------+
| CountryCode | List                                        |
+-------------+---------------------------------------------+
| ABW         | Dutch (Official),English,Papiamento,Spanish |
. ...         . ...                                         .
| ZWE         | English (Official),Ndebele,Nyanja,Shona     |
+-------------+---------------------------------------------+

Example 3: Using a custom separator

示例 3：使用自定义分隔符

SELECT   CountryCode
,        GROUP_CONCAT(Language SEPARATOR ' and ') AS List
FROM     CountryLanguage
GROUP BY CountryCode

Output:

输出：

+-------------+----------------------------------------------+
| CountryCode | List                                         |
+-------------+----------------------------------------------+
| ABW         | Dutch and English and Papiamento and Spanish |
. ...         . ...                                          .
| ZWE         | English and Ndebele and Nyanja and Shona     |
+-------------+----------------------------------------------+

Example 4: Controlling the order of the list elements

示例 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

Output:

输出：

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | English,Papiamento,Spanish,Dutch,  |
. ...         . ...                                .
| ZWE         | Ndebele,Nyanja,Shona,English       |
+-------------+------------------------------------+

Feature: COUNT(DISTINCT ) with multiple expressions

功能： COUNT(DISTINCT ) 具有多个表达式

You can use multiple expressions in a COUNT(DISTINCT ...) expression to count the number of combinations.

您可以在 COUNT(DISTINCT ...) 表达式中使用多个表达式来计算组合的数量。

SELECT COUNT(DISTINCT CountryCode, Language) FROM CountryLanguage

Feature / Gotcha: No need to include non-aggregated expressions in the GROUP BY list

功能/问题：无需在 GROUP BY 列表中包含非聚合表达式

Most RDBMS-es enforce a SQL92 compliant GROUP BY which requires all non-aggregated expressions in the SELECT list to appear in the GROUP BY. In these RDBMS-es, this statement:

大多数 RDBMS 强制执行符合 SQL92 的 GROUP BY，它要求 SELECT 列表中的所有非聚合表达式出现在 GROUP BY 中。在这些 RDBMS-es 中，此语句：

SELECT     Country.Code, Country.Continent, COUNT(CountryLanguage.Language)
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

is not valid, because the SELECT list contains the non-aggregated column Country.Continent which does not appear in the GROUP BY list. In these RDBMS-es, you must either modify the GROUP BY list to read

无效，因为 SELECT 列表包含未出现在 GROUP BY 列表中的非聚合列 Country.Continent。在这些 RDBMS-es 中，您必须修改 GROUP BY 列表以读取

GROUP BY   Country.Code, Country.Continent

or you must add some non-sense aggregate to Country.Continent, for example

或者您必须向 Country.Continent 添加一些无意义的聚合，例如

SELECT     Country.Code, MAX(Country.Continent), COUNT(CountryLanguage.Language)

Now, the thing is, logically there is nothing that demands that Country.Continent should be aggreagated. See, Country.Code is the primary key of the Country table. Country.Continent is also a column from the Country table and is thus by definitions functionally dependent upon the primary key Country.Code. So, there must exist exactly one value in Country.Continent for each distinct Country.Code. If you realize that, than you realize that it does not make sense to aggregate it (there is just one value, right) nor to group by it (as it won't make the result more unique as you're already grouping by on the pk)

现在，问题是，从逻辑上讲，没有什么要求 Country.Continent 应该聚合。看，Country.Code 是 Country 表的主键。Country.Continent 也是 Country 表中的一列，因此根据定义，它在功能上依赖于主键 Country.Code。因此，对于每个不同的 Country.Code，Country.Continent 中必须存在一个值。如果您意识到这一点，那么您就会意识到聚合它（只有一个值，对）或按它分组是没有意义的（因为它不会使结果更独特，因为您已经在分组pk)

Anyway - MySQL lets you include non-aggregated columns in the SELECT list without requiring you to also add them to the GROUP BY clause.

无论如何 - MySQL 允许您在 SELECT 列表中包含非聚合列，而无需您将它们添加到 GROUP BY 子句中。

The gotcha with this is that MySQL does not protect you in case you happen to use a non-aggregated column. So, a query like this:

问题在于，如果您碰巧使用非聚合列，MySQL 不会保护您。所以，像这样的查询：

SELECT     Country.Code, COUNT(CountryLanguage.Language), CountryLanguage.Percentage
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

Will be executed without complaint, but the CountryLanguage.Percentage column will contain non-sense (that is to say, of all languages percentages, one of the available values for the percentage will be picked at random or at least outside your control.

将在没有投诉的情况下执行，但 CountryLanguage.Percentage 列将包含无意义（也就是说，在所有语言百分比中，百分比的可用值之一将随机选择或至少在您的控制范围之外。

See: Debunking Group By Myths

请参阅：通过神话揭穿集团

Answer 8

回答by MarkR

The "pager" command in the client

客户端中的“pager”命令

If you've got, say, 10,000 rows in your result and want to view them (This assumes the "less" and "tee" commands available, which is normally the case under Linux; in Windows YMMV.)

如果您的结果中有 10,000 行并且想要查看它们（假设“less”和“tee”命令可用，这在 Linux 下通常是这种情况；在 Windows YMMV 中。）

pager less
select lots_of_stuff FROM tbl WHERE clause_which_matches_10k_rows;

And you'll get them in the "less" file viewer so you can page through them nicely, search etc.

您将在“较少”文件查看器中获取它们，以便您可以很好地翻阅它们，进行搜索等。

Also

还

pager tee myfile.txt
select a_few_things FROM tbl WHERE i_want_to_save_output_to_a_file;

Will conveniently write to a file.

将方便地写入文件。

Answer 9

回答by SorinV

Some things you may find interesting:

您可能会发现一些有趣的事情：

<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

Answer 10

回答by Eddy

Not a hidden feature, but useful nonetheless: http://mtop.sourceforge.net/

不是隐藏的功能，但仍然有用：http: //mtop.sourceforge.net/

MySQL MySQL的隐藏功能

提问by GateKiller

回答by Mike Trader

Administration:

行政：

PRIMARY KEY:

首要的关键：

TIMESTAMPS:

时间戳：

Binary Protocol:

二进制协议：

Master Server:

主服务器：

Normalization:

正常化：

UDF functions

UDF 函数

DataTypes:

数据类型：

Misc:

杂项：

Useful Commands:

有用的命令：

Tuning Queries

调优查询

回答by mat

回答by Eran Galperin

回答by CMS

回答by Alnitak

回答by Kornel

回答by Kornel

回答by MarkR

回答by SorinV

回答by Eddy

相关推荐

最近更新

标签

MySQL MySQL的隐藏功能

提问by GateKiller

回答by Mike Trader

Administration:

行政：

PRIMARY KEY:

首要的关键：

TIMESTAMPS:

时间戳：

Binary Protocol:

二进制协议：

Master Server:

主服务器：

Normalization:

正常化：

UDF functions

UDF 函数

DataTypes:

数据类型：

Misc:

杂项：

Useful Commands:

有用的命令：

Tuning Queries

调优查询

回答by mat

回答by Eran Galperin

回答by CMS

回答by Alnitak

回答by Kornel

回答by Kornel

回答by MarkR

回答by SorinV

回答by Eddy

相关推荐

MySQL mysql匹配字符串与表中字符串的开头

MySQL 字符集和排序规则到底是什么意思？

我厚吗？MySQL：LEFT JOIN 上的“非唯一表/别名”

绕过 MySQL“无法重新打开表”错误

相关推荐

最近更新

标签