隐藏的MySQL特性 [英] Hidden Features of MySQL

问题描述

我多年来一直使用 Microsoft SQL Server ，但刚刚刚刚开始对我的网络应用程序使用 MySQL ，我渴望获得知识。

要继续使用隐藏功能问题的长行，我会喜欢了解MySQL的任何隐藏或方便的功能，这将有希望提高我的知识这个开源数据库。

解决方案

一般来说，我今天调整的所有SQL都需要使用子查询。来自Oracle数据库世界，我认为理所当然的事情不是与MySQL一样工作。我在MySQL调优上的阅读使我得出结论：MySQL在优化查询方面落后于Oracle。

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

管理：

max_connections 是并发连接的数量。默认值为100个连接（自5.0以来为151个） - 非常小。

注意：

并且您的操作系统可能无法处理大量连接。

Linux / x86的MySQL二进制文件允许最多4096个并发连接，但是自编译的二进制文件通常没有限制。

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

注意：

以前的2个参数可能需要很多打开的文件。 20 + max_connections + table_cache * 2是一个很好的估计，你需要什么。 Linux上的MySQL有一个open_file_limit选项，设置此限制。

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

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

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

PRIMARY KEY：

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

KEY通常是INDEX的同义词。在列定义中给出时，也可以将键属性PRIMARY KEY指定为只有KEY。

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

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

在MySQL中，PRIMARY KEY的名称是PRIMARY

目前，仅InnoDB（v5.1？）表支持外键。

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

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

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

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

  ALTER TABLE mytable AUTO_INCREMENT = value; 
  

或
SET INSERT_ID = value;

$ b b

除非另有说明，否则值将以：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子句中使用这些类型之一时，最好是do
WHERE datecolumn = FROM_UNIXTIME（1057941242）
而不是
WHERE UNIX_TIMESTAMP（datecolumn）= 1057941242.
执行后者不会利用该列上的索引。 / p>

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中将datetime转换为unix时间戳，则

然后添加24小时：

然后将它转换回datetime它神奇地失去一个小时！

这是发生了什么。当将unix时间戳转换回datetime时，考虑时区，它恰好发生，在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列中的值。

注意：仅当更改字段时，才更新 UPDATE结果没有字段被更改，然后DateTime不更新！

另外，第一个TIMESTAMP默认情况下总是AUTOUPDATE，即使没有指定

当使用Dates时，我几乎总是认为Julian日期因为数据数学是一个简单的事情，添加或追踪整数，和秒从午夜以后为同样的原因。很少需要比秒更精细的粒度的时间。

这些都可以存储为一个4字节的整数，如果空间真的很紧，可以结合到UNIX

'secs in 24Hrs = 86400

这是一个无符号整数，直到2106年为止的时间（自纪元1/1/1970以来的秒数）

'签名整数max val = 2,147,483,647 - 可以持有68年的秒数

'无符号整数最大val = 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

GRANT REPLICATION SLAVE ON to slave_user IDENTIFIED BY'slave_password'

  #Master二进制日志配置STATEMENT导致复制
基于语句 - 默认
 
 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

$删除它们的子集b $ b

- 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

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 /

需要注意的一点是，在包含CHAR和VARCHAR的混合表中，mySQL会将CHAR更改为VARCHAR的

RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT，PRIMARY KEY（RecNum）

MySQL总是用年份表示日期，符合标准SQL和ISO 8601规范

其他：

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

- datadir将指定数据目录，

- skip-innodb将关闭the inno option and save you 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管理员备份功能，这是耗时的（恢复） / p>

诀窍是复制这些文件，然后DROP表。当你把文件放回
MySQl会识别他们并更新表跟踪。

如果你必须备份/恢复，

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

  SET AUTOCOMMIT = 0; 
 SET FOREIGN_KEY_CHECKS = 0; 
 
 ..你的转储文件.. 
 
 SET FOREIGN_KEY_CHECKS = 1; 
 COMMIT; 
 SET AUTOCOMMIT = 1; 
  

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

默认情况下，autocommit为on，这意味着
中的每个插入命令的转储文件将被视为单独的事务并写入磁盘下一个开始。如果不添加这些命令，将大型数据库重新加载到InnoDB可能需要许多小时...

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

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

VARCHAR值在存储时不进行填充。

比较MySQL中的CHAR和VARCHAR值时，不考虑尾随空格，而是保存和检索
值时将保留尾随空格。

如果整个记录是固定大小，使用CHAR只会加速您的访问。也就是说，
如果你使用任何可变大小的对象，你可能会使所有的可变大小。
在包含VARCHAR的表中使用CHAR不会获得速度。

由于MySQL的VARCHAR限制为255个字符，所以将其增加到65535个字符5.0.3

仅支持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，...

SHOW COLUMNS FROM mytable

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（）

获取当前线程中插入的最后一行的PK max（pkcolname） p>

注意：如果表为空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

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

 未知列'数据的第一位要放入表'in'字段列表'
 使用类似
 的I​​NSERT INTO表格）VALUES（$ this，$ that）
  
这是因为你没有任何撇号你试图坚持在桌子上。因此，您应该将您的代码更改为：
  INSERT INTO表（此为）VALUES（'$ this'，'$ '）
  
提醒，``用于定义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 
 
I've been working with Microsoft SQL Server with many years now but have only just recently started to use MySQL with my web applications, and I'm hungry for knowledge.

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.
解决方案
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. 

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.

Administration:

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

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.

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.

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.

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.

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.

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.

PRIMARY KEY:

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

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.

A PRIMARY KEY is a unique index where all key columns must be defined as 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.

In MySQL, the name of a PRIMARY KEY is PRIMARY

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

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.

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

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.)

To change the value of the AUTO_INCREMENT counter to be used for new rows:
ALTER TABLE mytable AUTO_INCREMENT = value; 
or
    SET INSERT_ID = value;

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

...) 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.

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

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.

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!

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. 

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.

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

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

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

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.

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:

' secs in 24Hrs = 86400

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

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

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)    

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

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
The 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. 

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:

One thing to note is that binary data CAN be sent inside a regular query if you escape it and remember MySQL requires only that 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.

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'
#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

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

Normalization:

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

UDF functions

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

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

Misc:

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

--datadir will specify the data directory and

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

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

Download Chapter 7 - Free

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: 

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. 

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

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.

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. 

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...

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

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

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

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

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.

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

Full-text searches are supported for MyISAM tables only. 

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    

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.

Useful Commands:

check strict mode: 
SELECT @@global.sql_mode;

turn off strict mode:

SET @@global.sql_mode= ''; 

SET @@global.sql_mode='MYSQL40'

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

SHOW COLUMNS FROM mytable

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

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

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). 

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.

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 ;)

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 free database. I develop application data servers on windows platforms mostly with MySQL. The worst mess I had to straighten out was

The ultimate MySQL legacy database nightmare

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

                        
这篇关于隐藏的MySQL特性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！