| List: | Internals | « Previous MessageNext Message » | |
| From: | paul | Date: | June 16 2005 1:52am |
| Subject: | bk commit - mysqldoc@docsrva tree (paul:1.2796) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local mysqldoc repository of paul. When paul does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://www.mysql.com/doc/I/n/Installing_source_tree.html ChangeSet 1.2796 05/06/15 18:52:39 paul@stripped +97 -0 Docs/manual.texi is obsolete. Long live refman/manual.xml. tools/manual-add-ui-tags.pl 1.2 05/06/15 18:52:38 paul@stripped +2 -1 Account for new mysql prompt. tools/add-ui-test.xml 1.2 05/06/15 18:52:38 paul@stripped +2 -1 Account for new mysql prompt. make.d/xml-prep 1.2 05/06/15 18:52:38 paul@stripped +4 -1 Add DocBook bug reference convertor. make.d/vars-docbook 1.4 05/06/15 18:52:38 paul@stripped +5 -0 Add DocBook bug reference convertor. Docs/Makefile 1.43 05/06/15 18:52:38 paul@stripped +1 -1 Still need Texinfo manual for generating help tables. Docs/manual-obsolete.texi 1.3006 05/06/15 18:48:07 paul@stripped +0 -0 Rename: Docs/manual.texi -> Docs/manual-obsolete.texi refman/views.xml 1.1 05/06/15 18:46:58 paul@stripped +793 -0 refman/using-mysql-programs.xml 1.1 05/06/15 18:46:58 paul@stripped +1242 -0 refman/tutorial.xml 1.1 05/06/15 18:46:58 paul@stripped +4218 -0 refman/triggers.xml 1.1 05/06/15 18:46:58 paul@stripped +417 -0 refman/todo.xml 1.1 05/06/15 18:46:58 paul@stripped +828 -0 refman/stored-procedures.xml 1.1 05/06/15 18:46:58 paul@stripped +1391 -0 refman/storage-engines.xml 1.1 05/06/15 18:46:58 paul@stripped +3369 -0 refman/sql-syntax.xml 1.1 05/06/15 18:46:58 paul@stripped +16671 -0 refman/spatial-extensions-in-mysql.xml 1.1 05/06/15 18:46:58 paul@stripped +4732 -0 refman/replication.xml 1.1 05/06/15 18:46:58 paul@stripped +4059 -0 refman/regexp.xml 1.1 05/06/15 18:46:58 paul@stripped +492 -0 refman/problems.xml 1.1 05/06/15 18:46:58 paul@stripped +4624 -0 refman/preface.xml 1.1 05/06/15 18:46:58 paul@stripped +22 -0 refman/precision-math.xml 1.1 05/06/15 18:46:58 paul@stripped +1172 -0 refman/porting.xml 1.1 05/06/15 18:46:58 paul@stripped +1349 -0 refman/news.xml 1.1 05/06/15 18:46:58 paul@stripped +33347 -0 refman/ndbcluster.xml 1.1 05/06/15 18:46:58 paul@stripped +9622 -0 refman/mysql-optimization.xml 1.1 05/06/15 18:46:58 paul@stripped +7820 -0 refman/mysql-floss-license-exception.xml 1.1 05/06/15 18:46:58 paul@stripped +343 -0 refman/mysql-database-administration.xml 1.1 05/06/15 18:46:58 paul@stripped +23532 -0 refman/mysql-connectors.xml 1.1 05/06/15 18:46:58 paul@stripped +8425 -0 refman/mysql-apis.xml 1.1 05/06/15 18:46:58 paul@stripped +13122 -0 refman/maxdb.xml 1.1 05/06/15 18:46:58 paul@stripped +1118 -0 refman/manual.xml 1.1 05/06/15 18:46:58 paul@stripped +90 -0 refman/language-structure.xml 1.1 05/06/15 18:46:58 paul@stripped +2939 -0 refman/views.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/views.xml refman/using-mysql-programs.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/using-mysql-programs.xml refman/tutorial.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/tutorial.xml refman/triggers.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/triggers.xml refman/todo.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/todo.xml refman/titles.ent 1.1 05/06/15 18:46:57 paul@stripped +1518 -0 refman/stored-procedures.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/stored-procedures.xml refman/storage-engines.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/storage-engines.xml refman/sql-syntax.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/sql-syntax.xml refman/spatial-extensions-in-mysql.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/spatial-extensions-in-mysql.xml refman/replication.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/replication.xml refman/regexp.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/regexp.xml refman/problems.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/problems.xml refman/preface.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/preface.xml refman/precision-math.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/precision-math.xml refman/porting.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/porting.xml refman/news.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/news.xml refman/ndbcluster.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/ndbcluster.xml refman/mysql-optimization.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/mysql-optimization.xml refman/mysql-floss-license-exception.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/mysql-floss-license-exception.xml refman/mysql-database-administration.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/mysql-database-administration.xml refman/mysql-connectors.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/mysql-connectors.xml refman/mysql-apis.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/mysql-apis.xml refman/maxdb.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/maxdb.xml refman/manual.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/manual.xml refman/language-structure.xml 1.0 05/06/15 18:46:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/language-structure.xml refman/introduction.xml 1.1 05/06/15 18:46:57 paul@stripped +5059 -0 refman/installing.xml 1.1 05/06/15 18:46:57 paul@stripped +17543 -0 refman/innodb.xml 1.1 05/06/15 18:46:57 paul@stripped +6499 -0 refman/information-schema.xml 1.1 05/06/15 18:46:57 paul@stripped +3502 -0 refman/gpl-license.xml 1.1 05/06/15 18:46:57 paul@stripped +499 -0 refman/functions.xml 1.1 05/06/15 18:46:57 paul@stripped +13951 -0 refman/fixedchars.ent 1.1 05/06/15 18:46:57 paul@stripped +240 -0 refman/extending-mysql.xml 1.1 05/06/15 18:46:57 paul@stripped +2128 -0 refman/error-handling.xml 1.1 05/06/15 18:46:57 paul@stripped +4415 -0 refman/environment-variables.xml 1.1 05/06/15 18:46:57 paul@stripped +427 -0 refman/credits.xml 1.1 05/06/15 18:46:57 paul@stripped +1888 -0 refman/column-types.xml 1.1 05/06/15 18:46:57 paul@stripped +5457 -0 refman/client-side-scripts.xml 1.1 05/06/15 18:46:57 paul@stripped +6783 -0 refman/charset.xml 1.1 05/06/15 18:46:57 paul@stripped +3986 -0 refman/titles.ent 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/titles.ent refman/introduction.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/introduction.xml refman/installing.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/installing.xml refman/innodb.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/innodb.xml refman/information-schema.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/information-schema.xml refman/gpl-license.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/gpl-license.xml refman/functions.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/functions.xml refman/fixedchars.ent 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/fixedchars.ent refman/extending-mysql.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/extending-mysql.xml refman/error-handling.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/error-handling.xml refman/environment-variables.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/environment-variables.xml refman/credits.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/credits.xml refman/column-types.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/column-types.xml refman/client-side-scripts.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/client-side-scripts.xml refman/charset.xml 1.0 05/06/15 18:46:57 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/charset.xml refman/images/myflowchart.png 1.1 05/06/15 18:46:37 paul@stripped +627 -0 refman/images/myflowchart.pdf 1.1 05/06/15 18:46:37 paul@stripped +874 -0 refman/images/myflowchart.graffle 1.1 05/06/15 18:46:37 paul@stripped +1104 -0 refman/images/myflowchart.gif 1.1 05/06/15 18:46:37 paul@stripped +9921 -0 refman/images/mydsn.png 1.1 05/06/15 18:46:37 paul@stripped +2395 -0 refman/images/mydsn.pdf 1.1 05/06/15 18:46:37 paul@stripped +1885 -0 refman/images/mydsn.jpg 1.1 05/06/15 18:46:37 paul@stripped +617 -0 refman/images/mydsn-trace.png 1.1 05/06/15 18:46:37 paul@stripped +2508 -0 refman/images/mydsn-trace.pdf 1.1 05/06/15 18:46:37 paul@stripped +1910 -0 refman/images/mydsn-trace.jpg 1.1 05/06/15 18:46:37 paul@stripped +662 -0 refman/images/mydsn-test-success.png 1.1 05/06/15 18:46:37 paul@stripped +153 -0 refman/images/mydsn-test-success.pdf 1.1 05/06/15 18:46:37 paul@stripped +169 -0 refman/images/mydsn-test-success.jpg 1.1 05/06/15 18:46:37 paul@stripped +194 -0 refman/images/mydsn-test-fail.png 1.1 05/06/15 18:46:37 paul@stripped +166 -0 refman/images/mydsn-test-fail.pdf 1.1 05/06/15 18:46:37 paul@stripped +178 -0 refman/images/mydsn-test-fail.jpg 1.1 05/06/15 18:46:37 paul@stripped +216 -0 refman/images/mydsn-setup.png 1.1 05/06/15 18:46:37 paul@stripped +338 -0 refman/images/mydsn-setup.pdf 1.1 05/06/15 18:46:37 paul@stripped +298 -0 refman/images/mydsn-setup.jpg 1.1 05/06/15 18:46:37 paul@stripped +682 -0 refman/images/mydsn-options.png 1.1 05/06/15 18:46:37 paul@stripped +350 -0 refman/images/mydsn-options.pdf 1.1 05/06/15 18:46:37 paul@stripped +304 -0 refman/images/mydsn-options.jpg 1.1 05/06/15 18:46:37 paul@stripped +942 -0 refman/images/mydsn-icon.png 1.1 05/06/15 18:46:37 paul@stripped +63 -0 refman/images/mydsn-icon.pdf 1.1 05/06/15 18:46:37 paul@stripped +94 -0 refman/images/mydsn-icon.gif 1.1 05/06/15 18:46:37 paul@stripped +56 -0 refman/images/mydsn-example.png 1.1 05/06/15 18:46:37 paul@stripped +343 -0 refman/images/mydsn-example.pdf 1.1 05/06/15 18:46:37 paul@stripped +301 -0 refman/images/mydsn-example.jpg 1.1 05/06/15 18:46:37 paul@stripped +698 -0 refman/images/mydll-properties.png 1.1 05/06/15 18:46:37 paul@stripped +261 -0 refman/images/mydll-properties.pdf 1.1 05/06/15 18:46:37 paul@stripped +243 -0 refman/images/mydll-properties.jpg 1.1 05/06/15 18:46:37 paul@stripped +588 -0 refman/images/myarchitecture.png 1.1 05/06/15 18:46:37 paul@stripped +175 -0 refman/images/myarchitecture.pdf 1.1 05/06/15 18:46:37 paul@stripped +427 -0 refman/images/myarchitecture.graffle 1.1 05/06/15 18:46:37 paul@stripped +670 -0 refman/images/myarchitecture.gif 1.1 05/06/15 18:46:37 paul@stripped +3259 -0 refman/images/myaccess.png 1.1 05/06/15 18:46:37 paul@stripped +2849 -0 refman/images/myaccess.pdf 1.1 05/06/15 18:46:37 paul@stripped +2561 -0 refman/images/myaccess.jpg 1.1 05/06/15 18:46:37 paul@stripped +643 -0 refman/images/myaccess-odbc.png 1.1 05/06/15 18:46:37 paul@stripped +420 -0 refman/images/myaccess-odbc.pdf 1.1 05/06/15 18:46:37 paul@stripped +347 -0 refman/images/myaccess-odbc.gif 1.1 05/06/15 18:46:37 paul@stripped +306 -0 refman/images/multi-comp-1.vsd 1.1 05/06/15 18:46:37 paul@stripped +3847 -0 refman/images/multi-comp-1.svg 1.1 05/06/15 18:46:37 paul@stripped +1010 -0 refman/images/multi-comp-1.png 1.1 05/06/15 18:46:37 paul@stripped +516 -0 refman/images/multi-comp-1.pdf 1.1 05/06/15 18:46:37 paul@stripped +814 -0 refman/images/multi-comp-1.jpg 1.1 05/06/15 18:46:37 paul@stripped +1392 -0 refman/images/multi-comp-1-reduced.png 1.1 05/06/15 18:46:37 paul@stripped +638 -0 refman/images/cluster-components-1.png 1.1 05/06/15 18:46:37 paul@stripped +1673 -0 refman/images/cluster-components-1.pdf 1.1 05/06/15 18:46:37 paul@stripped +1222 -0 refman/images/cluster-components-1.jpg 1.1 05/06/15 18:46:37 paul@stripped +1307 -0 refman/images/myflowchart.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myflowchart.png refman/images/myflowchart.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myflowchart.pdf refman/images/myflowchart.graffle 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myflowchart.graffle refman/images/myflowchart.gif 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myflowchart.gif refman/images/mydsn.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn.png refman/images/mydsn.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn.pdf refman/images/mydsn.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn.jpg refman/images/mydsn-trace.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-trace.png refman/images/mydsn-trace.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-trace.pdf refman/images/mydsn-trace.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-trace.jpg refman/images/mydsn-test-success.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-test-success.png refman/images/mydsn-test-success.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-test-success.pdf refman/images/mydsn-test-success.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-test-success.jpg refman/images/mydsn-test-fail.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-test-fail.png refman/images/mydsn-test-fail.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-test-fail.pdf refman/images/mydsn-test-fail.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-test-fail.jpg refman/images/mydsn-setup.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-setup.png refman/images/mydsn-setup.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-setup.pdf refman/images/mydsn-setup.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-setup.jpg refman/images/mydsn-options.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-options.png refman/images/mydsn-options.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-options.pdf refman/images/mydsn-options.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-options.jpg refman/images/mydsn-icon.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-icon.png refman/images/mydsn-icon.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-icon.pdf refman/images/mydsn-icon.gif 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-icon.gif refman/images/mydsn-example.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-example.png refman/images/mydsn-example.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-example.pdf refman/images/mydsn-example.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydsn-example.jpg refman/images/mydll-properties.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydll-properties.png refman/images/mydll-properties.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydll-properties.pdf refman/images/mydll-properties.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/mydll-properties.jpg refman/images/myarchitecture.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myarchitecture.png refman/images/myarchitecture.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myarchitecture.pdf refman/images/myarchitecture.graffle 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myarchitecture.graffle refman/images/myarchitecture.gif 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myarchitecture.gif refman/images/myaccess.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myaccess.png refman/images/myaccess.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myaccess.pdf refman/images/myaccess.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myaccess.jpg refman/images/myaccess-odbc.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myaccess-odbc.png refman/images/myaccess-odbc.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myaccess-odbc.pdf refman/images/myaccess-odbc.gif 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/myaccess-odbc.gif refman/images/multi-comp-1.vsd 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/multi-comp-1.vsd refman/images/multi-comp-1.svg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/multi-comp-1.svg refman/images/multi-comp-1.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/multi-comp-1.png refman/images/multi-comp-1.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/multi-comp-1.pdf refman/images/multi-comp-1.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/multi-comp-1.jpg refman/images/multi-comp-1-reduced.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/multi-comp-1-reduced.png refman/images/cluster-components-1.png 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/cluster-components-1.png refman/images/cluster-components-1.pdf 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/cluster-components-1.pdf refman/images/cluster-components-1.jpg 1.0 05/06/15 18:46:37 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/refman/images/cluster-components-1.jpg tools/bug-prep.pl 1.1 05/06/15 18:36:58 paul@stripped +23 -0 tools/bug-prep.pl 1.0 05/06/15 18:36:58 paul@stripped +0 -0 BitKeeper file /src/extern/MySQL/bk/mysqldoc/tools/bug-prep.pl # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: paul # Host: snake-hub.snake.net # Root: /src/extern/MySQL/bk/mysqldoc --- 1.1/make.d/xml-prep Fri May 6 22:15:24 2005 +++ 1.2/make.d/xml-prep Wed Jun 15 18:52:38 2005 @@ -8,8 +8,11 @@ .PRECIOUS: %-prepped.xml +# BUG_PREP fixes up "Bug #xxxx" bug references into <ulink> elements +# that point to htto://bugs.mysql.com bug reports. + %-prepped.xml: %.xml $(DBK_PREP_XSL) - $(XSLTPROC) --output $@ $(DBK_PREP_XSL) $< + $(XSLTPROC) $(DBK_PREP_XSL) $< | $(BUG_PREP) > $@ clean:: $(RM) *-prepped.xml --- 1.3/make.d/vars-docbook Fri May 6 22:15:23 2005 +++ 1.4/make.d/vars-docbook Wed Jun 15 18:52:38 2005 @@ -28,3 +28,8 @@ STEM_LANG_CODE = $(TOOLS_DIR)/stem-lang-code STEM_LANG_ENCODING = $(TOOLS_DIR)/stem-lang-code --encoding + +# BUG_PREP fixes up "Bug #xxxx" bug references into <ulink> elements +# that point to htto://bugs.mysql.com bug reports. + +BUG_PREP = $(TOOLS_DIR)/bug-prep.pl --- 1.1/tools/add-ui-test.xml Fri Jul 16 11:56:45 2004 +++ 1.2/tools/add-ui-test.xml Wed Jun 15 18:52:38 2005 @@ -10,7 +10,8 @@ -> that '> continues "> for - `> several lines + `> several + /*> lines C:\> this is a Windows command C:\mysql\bin> this is a Windows command </programlisting> --- 1.1/tools/manual-add-ui-tags.pl Fri Jul 16 11:56:45 2004 +++ 1.2/tools/manual-add-ui-tags.pl Wed Jun 15 18:52:38 2005 @@ -42,6 +42,7 @@ # '> # "> # `> +# /*> # shell> # C:\[^>]*> (this is a pattern that attempts to match any Windows prompt)> @@ -142,7 +143,7 @@ # Check whether line begins with one of the known prompts. # If so, tag it after cleaning any existing tags first. # Otherwise, echo it unchanged. - if (/^((?:mysql| [-'"`])(?:>|>))(\s*)(.*)/ # mysql prompts + if (/^((?:mysql| [-'"`]| \/\*)(?:>|>))(\s*)(.*)/ # mysql prompts || /^(shell(?:>|>))(\s*)(.*)/ # shell prompt || /^(C:\\[^>&]*(?:>|>))(\s*)(.*)/) # Windows prompt { --- New file --- +++ refman/charset.xml 05/06/15 18:46:57 <?xml version='1.0' encoding='utf-8'?> <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN" "http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" [ <!ENTITY % fixedchars.entities SYSTEM "fixedchars.ent"> %fixedchars.entities; <!ENTITY % title.entities SYSTEM "titles.ent"> %title.entities; ]> <chapter id='charset'> <title id='title-charset'>&title-charset;</title> <indexterm type="concept"> <primary>Character sets</primary> </indexterm> <indexterm type="concept"> <primary>Unicode</primary> </indexterm> <indexterm type="concept"> <primary>UTF8</primary> </indexterm> <indexterm type="concept"> <primary>UTF-8</primary> </indexterm> <indexterm type="concept"> <primary>UCS-2</primary> </indexterm> <!-- TODO: --> <!-- Note: Put credits in contributors section. --> <!-- Add indexing. --> <!-- Add info about how to configure in various charsets --> <!-- ENUM and SET can have character set clauses as well --> <para> Improved support for character set handling was added to MySQL in Version 4.1. The features described here are as implemented in MySQL 4.1.1. (MySQL 4.1.0 has some but not all of these features, and some of them are implemented differently.) </para> <para> This chapter discusses the following topics: </para> <itemizedlist> <listitem><para> What are character sets and collations? </para></listitem> <listitem><para> The multiple-level default system </para></listitem> <listitem><para> New syntax in MySQL 4.1 </para></listitem> <listitem><para> Affected functions and operations </para></listitem> <listitem><para> Unicode support </para></listitem> <listitem><para> The meaning of each individual character set and collation </para></listitem> </itemizedlist> <para> Character set support currently is included in the <literal>MyISAM</literal>, <literal>MEMORY</literal> (<literal>HEAP</literal>), and (as of MySQL 4.1.2) <literal>InnoDB</literal> storage engines. The <literal>ISAM</literal> storage engine does not include character set support; there are no plans to change this, because <literal>ISAM</literal> is deprecated. </para> <section id="charset-general"> <title id='title-charset-general'>&title-charset-general;</title> <para> A <emphasis role="bold">character set</emphasis> is a set of symbols and encodings. A <emphasis role="bold">collation</emphasis> is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set. </para> <para> Suppose that we have an alphabet with four letters: '<literal>A</literal>', '<literal>B</literal>', '<literal>a</literal>', '<literal>b</literal>'. We give each letter a number: '<literal>A</literal>' = 0, '<literal>B</literal>' = 1, '<literal>a</literal>' = 2, '<literal>b</literal>' = 3. The letter '<literal>A</literal>' is a symbol, the number 0 is the <emphasis role="bold">encoding</emphasis> for '<literal>A</literal>', and the combination of all four letters and their encodings is a <emphasis role="bold">character set</emphasis>. </para> <para> Suppose that we want to compare two string values, '<literal>A</literal>' and '<literal>B</literal>'. The simplest way to do this is to look at the encodings: 0 for '<literal>A</literal>' and 1 for '<literal>B</literal>'. Because 0 is less than 1, we say '<literal>A</literal>' is less than '<literal>B</literal>'. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): ``compare the encodings.'' We call this simplest of all possible collations a <emphasis role="bold">binary</emphasis> collation. </para> <para> But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters '<literal>a</literal>' and '<literal>b</literal>' as equivalent to '<literal>A</literal>' and '<literal>B</literal>'; (2) then compare the encodings. We call this a <emphasis role="bold">case-insensitive</emphasis> collation. It's a little more complex than a binary collation. </para> <para> In real life, most character sets have many characters: not just '<literal>A</literal>' and '<literal>B</literal>' but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules: not just case insensitivity but also accent insensitivity (an ``accent'' is a mark attached to a character as in German '<literal>Ö</literal>') and multiple-character mappings (such as the rule that '<literal>Ö</literal>' = '<literal>OE</literal>' in one of the two German collations). </para> <para> MySQL 4.1 can do these things for you: </para> <itemizedlist> <listitem><para> Store strings using a variety of character sets </para></listitem> <listitem><para> Compare strings using a variety of collations </para></listitem> <listitem><para> Mix strings with different character sets or collations in the same server, the same database, or even the same table </para></listitem> <listitem><para> Allow specification of character set and collation at any level </para></listitem> </itemizedlist> <para> In these respects, not only is MySQL 4.1 far more flexible than MySQL 4.0, it also is far ahead of other DBMSs. However, to use the new features effectively, you need to learn what character sets and collations are available, how to change their defaults, and what the various string operators do with them. </para> </section> <section id="charset-mysql"> <title id='title-charset-mysql'>&title-charset-mysql;</title> <para> The MySQL server can support multiple character sets. To list the available character sets, use the <literal>SHOW CHARACTER SET</literal> statement: </para> <!-- NOTE: If you rerun the following command, trim off the Maxlen column. --> <!-- Otherwise, the lines are too long to fit nicely on the page. --> <programlisting> mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | ISO 8859-1 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | ... </programlisting> <para> Any given character set always has at least one collation. It may have several collations. </para> <para> To list the collations for a character set, use the <literal>SHOW COLLATION</literal> statement. For example, to see the collations for the <literal>latin1</literal> (``ISO-8859-1 West European'') character set, use this statement to find those collation names that begin with <literal>latin1</literal>: </para> <programlisting> mysql> SHOW COLLATION LIKE 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +-------------------+---------+----+---------+----------+---------+ </programlisting> <para> The <literal>latin1</literal> collations have the following meanings: </para> <informaltable> <tgroup cols="2"> <colspec colwidth="40*"/> <colspec colwidth="60*"/> <tbody> <row> <entry> <emphasis role="bold">Collation</emphasis> </entry> <entry> <emphasis role="bold">Meaning</emphasis> </entry> </row> <row> <entry> <literal>latin1_bin</literal> </entry> <entry> Binary according to <literal>latin1</literal> encoding </entry> </row> <row> <entry> <literal>latin1_danish_ci</literal> </entry> <entry> Danish/Norwegian </entry> </row> <row> <entry> <literal>latin1_general_ci</literal> </entry> <entry> Multilingual </entry> </row> <row> <entry> <literal>latin1_general_cs</literal> </entry> <entry> Multilingual, case sensitive </entry> </row> <row> <entry> <literal>latin1_german1_ci</literal> </entry> <entry> German DIN-1 </entry> </row> <row> <entry> <literal>latin1_german2_ci</literal> </entry> <entry> German DIN-2 </entry> </row> <row> <entry> <literal>latin1_spanish_ci</literal> </entry> <entry> Modern Spanish </entry> </row> <row> <entry> <literal>latin1_swedish_ci</literal> </entry> <entry> Swedish/Finnish </entry> </row> </tbody> </tgroup> </informaltable> <para> Collations have these general characteristics: </para> <itemizedlist> <listitem><para> Two different character sets cannot have the same collation. </para></listitem> <listitem><para> Each character set has one collation that is the <emphasis>default collation</emphasis>. For example, the default collation for <literal>latin1</literal> is <literal>latin1_swedish_ci</literal>. </para></listitem> <listitem><para> There is a convention for collation names: They start with the name of the character set with which they are associated, they usually include a language name, and they end with <literal>_ci</literal> (case insensitive), <literal>_cs</literal> (case sensitive), or <literal>_bin</literal> (binary). </para></listitem> </itemizedlist> </section> <section id="charset-defaults"> <title id='title-charset-defaults'>&title-charset-defaults;</title> <para> There are default settings for character sets and collations at four levels: server, database, table, and connection. The following description may appear complex, but it has been found in practice that multiple-level defaulting leads to natural and obvious results. </para> <section id="charset-server"> <title id='title-charset-server'>&title-charset-server;</title> <para> The MySQL Server has a server character set and a server collation, which may not be null. </para> <para> MySQL determines the server character set and server collation thus: </para> <itemizedlist> <listitem><para> According to the option settings in effect when the server starts </para></listitem> <listitem><para> According to the values set at runtime </para></listitem> </itemizedlist> <para> At the server level, the decision is simple. The server character set and collation depend initially on the options that you use when you start <command>mysqld</command>. You can use <literal>--default-character-set</literal> for the character set, and along with it you can add <literal>--default-collation</literal> for the collation. If you don't specify a character set, that is the same as saying <literal>--default-character-set=latin1</literal>. If you specify only a character set (for example, <literal>latin1</literal>) but not a collation, that is the same as saying <literal>--default-charset=latin1</literal> <literal>--default-collation=latin1_swedish_ci</literal> because <literal>latin1_swedish_ci</literal> is the default collation for <literal>latin1</literal>. Therefore, the following three commands all have the same effect: </para> <programlisting> shell> mysqld shell> mysqld --default-character-set=latin1 shell> mysqld --default-character-set=latin1 \ --default-collation=latin1_swedish_ci </programlisting> <para> One way to change the settings is by recompiling. If you want to change the default server character set and collation when building from sources, use: <literal>--with-charset</literal> and <literal>--with-collation</literal> as arguments for <command>configure</command>. For example: </para> <programlisting> shell> ./configure --with-charset=latin1 </programlisting> <para> Or: </para> <programlisting> shell> ./configure --with-charset=latin1 \ --with-collation=latin1_german1_ci </programlisting> <para> Both <command>mysqld</command> and <command>configure</command> verify that the character set/collation combination is valid. If not, each program displays an error message and terminates. </para> <para> The current server character set and collation are available as the values of the <literal>character_set_server</literal> and <literal>collation_server</literal> system variables. These variables can be changed at runtime. </para> </section> <section id="charset-database"> <title id='title-charset-database'>&title-charset-database;</title> <para> Every database has a database character set and a database collation, which may not be null. The <literal>CREATE DATABASE</literal> and <literal>ALTER DATABASE</literal> statements have optional clauses for specifying the database character set and collation: </para> <programlisting> CREATE DATABASE <replaceable>db_name</replaceable> [[DEFAULT] CHARACTER SET <replaceable>charset_name</replaceable>] [[DEFAULT] COLLATE <replaceable>collation_name</replaceable>] ALTER DATABASE <replaceable>db_name</replaceable> [[DEFAULT] CHARACTER SET <replaceable>charset_name</replaceable>] [[DEFAULT] COLLATE <replaceable>collation_name</replaceable>] </programlisting> <para> Example: </para> <programlisting> CREATE DATABASE <replaceable>db_name</replaceable> DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; </programlisting> <para> MySQL chooses the database character set and database collation thus: </para> <itemizedlist> <listitem><para> If both <literal>CHARACTER SET <replaceable>X</replaceable></literal> and <literal>COLLATE <replaceable>Y</replaceable></literal> were specified, then character set <replaceable>X</replaceable> and collation <replaceable>Y</replaceable>. </para></listitem> <listitem><para> If <literal>CHARACTER SET <replaceable>X</replaceable></literal> was specified without <literal>COLLATE</literal>, then character set <replaceable>X</replaceable> and its default collation. </para></listitem> <listitem><para> Otherwise, the server character set and server collation. </para></listitem> </itemizedlist> <para> MySQL's <literal>CREATE DATABASE ... DEFAULT CHARACTER SET ...</literal> syntax is analogous to the standard SQL <literal>CREATE SCHEMA ... CHARACTER SET ...</literal> syntax. Because of this, it is possible to create databases with different character sets and collations on the same MySQL server. </para> <para> The database character set and collation are used as default values if the table character set and collation are not specified in <literal>CREATE TABLE</literal> statements. They have no other purpose. </para> <para> The character set and collation for the default database are available as the values of the <literal>character_set_database</literal> and <literal>collation_database</literal> system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level variables, <literal>character_set_server</literal> and <literal>collation_server</literal>. </para> </section> <section id="charset-table"> <title id='title-charset-table'>&title-charset-table;</title> <para> Every table has a table character set and a table collation, which may not be null. The <literal>CREATE TABLE</literal> and <literal>ALTER TABLE</literal> statements have optional clauses for specifying the table character set and collation: </para> <programlisting> CREATE TABLE <replaceable>tbl_name</replaceable> (<replaceable>column_list</replaceable>) [DEFAULT CHARACTER SET <replaceable>charset_name</replaceable> [COLLATE <replaceable>collation_name</replaceable>]] ALTER TABLE <replaceable>tbl_name</replaceable> [DEFAULT CHARACTER SET <replaceable>charset_name</replaceable>] [COLLATE <replaceable>collation_name</replaceable>] </programlisting> <para> Example: </para> <programlisting> CREATE TABLE t1 ( ... ) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci; </programlisting> <para> MySQL chooses the table character set and collation thus: </para> <itemizedlist> <listitem><para> If both <literal>CHARACTER SET <replaceable>X</replaceable></literal> and <literal>COLLATE <replaceable>Y</replaceable></literal> were specified, then character set <replaceable>X</replaceable> and collation <replaceable>Y</replaceable>. </para></listitem> <listitem><para> If <literal>CHARACTER SET <replaceable>X</replaceable></literal> was specified without <literal>COLLATE</literal>, then character set <replaceable>X</replaceable> and its default collation. </para></listitem> <listitem><para> Otherwise, the database character set and collation. </para></listitem> </itemizedlist> <para> The table character set and collation are used as default values if the column character set and collation are not specified in individual column definitions. The table character set and collation are MySQL extensions; there are no such things in standard SQL. </para> </section> <section id="charset-column"> <title id='title-charset-column'>&title-charset-column;</title> <para> Every ``character'' column (that is, a column of type <literal>CHAR</literal>, <literal>VARCHAR</literal>, or <literal>TEXT</literal>) has a column character set and a column collation, which may not be null. Column definition syntax has optional clauses for specifying the column character set and collation: </para> <programlisting> <replaceable>col_name</replaceable> {CHAR | VARCHAR | TEXT} (<replaceable>col_length</replaceable>) [CHARACTER SET <replaceable>charset_name</replaceable> [COLLATE <replaceable>collation_name</replaceable>]] </programlisting> <para> Example: </para> <programlisting> CREATE TABLE Table1 ( column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci ); </programlisting> <para> MySQL chooses the column character set and collation thus: </para> <itemizedlist> <listitem><para> If both <literal>CHARACTER SET <replaceable>X</replaceable></literal> and <literal>COLLATE <replaceable>Y</replaceable></literal> were specified, then character set <replaceable>X</replaceable> and collation <replaceable>Y</replaceable>. </para></listitem> <listitem><para> If <literal>CHARACTER SET X</literal> was specified without <literal>COLLATE</literal>, then character set <literal>X</literal> and its default collation. </para></listitem> <listitem><para> Otherwise, the table character set and collation. </para></listitem> </itemizedlist> <para> The <literal>CHARACTER SET</literal> and <literal>COLLATE</literal> clauses are standard SQL. </para> </section> <section id="charset-examples"> <title id='title-charset-examples'>&title-charset-examples;</title> <para> The following examples show how MySQL determines default character set and collation values. </para> <para> <emphasis role="bold">Example 1: Table + Column Definition</emphasis> </para> <programlisting> CREATE TABLE t1 ( c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci ) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin; </programlisting> <para> Here we have a column with a <literal>latin1</literal> character set and a <literal>latin1_german1_ci</literal> collation. The definition is explicit, so that's straightforward. Notice that there's no problem storing a <literal>latin1</literal> column in a <literal>latin2</literal> table. </para> <para> <emphasis role="bold">Example 2: Table + Column Definition</emphasis> </para> <programlisting> CREATE TABLE t1 ( c1 CHAR(10) CHARACTER SET latin1 ) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci; </programlisting> <para> This time we have a column with a <literal>latin1</literal> character set and a default collation. Although it might seem natural, the default collation is not taken from the table level. Instead, because the default collation for <literal>latin1</literal> is always <literal>latin1_swedish_ci</literal>, column <literal>c1</literal> has a collation of <literal>latin1_swedish_ci</literal> (not <literal>latin1_danish_ci</literal>). </para> <para> <emphasis role="bold">Example 3: Table + Column Definition</emphasis> </para> <programlisting> CREATE TABLE t1 ( c1 CHAR(10) ) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci; </programlisting> <para> We have a column with a default character set and a default collation. In this circumstance, MySQL looks up to the table level for inspiration in determining the column character set and collation. So, the character set for column <literal>c1</literal> is <literal>latin1</literal> and its collation is <literal>latin1_danish_ci</literal>. </para> <para> <emphasis role="bold">Example 4: Database + Table + Column Definition</emphasis> </para> <programlisting> CREATE DATABASE d1 DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci; USE d1; CREATE TABLE t1 ( c1 CHAR(10) ); </programlisting> <para> We create a column without specifying its character set and collation. We're also not specifying a character set and a collation at the table level. In this circumstance, MySQL looks up to the database level for inspiration. (The database's settings become the table's settings, and thereafter become the column's setting.) So, the character set for column <literal>c1</literal> is <literal>latin2</literal> and its collation is <literal>latin2_czech_ci</literal>. </para> </section> <section id="charset-connection"> <title id='title-charset-connection'>&title-charset-connection;</title> <para> Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections: </para> <itemizedlist> <listitem><para> The server character set and collation are available as the values of the <literal>character_set_server</literal> and <literal>collation_server</literal> variables. </para></listitem> <listitem><para> The character set and collation of the default database are available as the values of the <literal>character_set_database</literal> and <literal>collation_database</literal> variables. </para></listitem> </itemizedlist> <para> Additional character set and collation variables are involved in handling traffic for the connection between a client and the server. Every client has connection-related character set and collation variables. </para> <para> Consider what a ``connection'' is: It's what you make when you connect to the server. The client sends SQL statements, such as queries, over the connection to the server. The server sends responses, such as result sets, over the connection back to the client. This leads to several questions about character set and collation handling for client connections, each of which can be answered in terms of system variables: </para> <itemizedlist> <listitem><para> What character set is the query in when it leaves the client? </para> <para> The server takes the <literal>character_set_client</literal> variable to be the character set in which queries are sent by the client. </para></listitem> <listitem><para> What character set should the server translate a query to after receiving it? </para> <para> For this, <literal>character_set_connection</literal> and <literal>collation_connection</literal> are used by the server. It converts queries sent by the client from <literal>character_set_client</literal> to <literal>character_set_connection</literal> (except for string literals that have an introducer such as <literal>_latin1</literal> or <literal>_utf8</literal>). <literal>collation_connection</literal> is important for comparisons of literal strings. For comparisons of strings with column values, it does not matter because columns have a higher collation precedence. </para></listitem> <listitem><para> What character set should the server translate to before shipping result sets or error messages back to the client? </para> <para> The <literal>character_set_results</literal> variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names. </para></listitem> </itemizedlist> <para> You can fine-tune the settings for these variables, or you can depend on the defaults (in which case, you can skip this section). </para> <para> There are two statements that affect the connection character sets: </para> <programlisting> SET NAMES '<replaceable>charset_name</replaceable>' SET CHARACTER SET <replaceable>charset_name</replaceable> </programlisting> <para> <literal>SET NAMES</literal> indicates what is in the SQL statements that the client sends. Thus, <literal>SET NAMES 'cp1251'</literal> tells the server ``future incoming messages from this client are in character set <literal>cp1251</literal>.'' It also specifies the character set for results that the server sends back to the client. (For example, it indicates what character set column values are if you use a <literal>SELECT</literal> statement.) </para> <para> A <literal>SET NAMES '<replaceable>x</replaceable>'</literal> statement is equivalent to these three statements: </para> <programlisting> mysql> SET character_set_client = <replaceable>x</replaceable>; mysql> SET character_set_results = <replaceable>x</replaceable>; mysql> SET character_set_connection = <replaceable>x</replaceable>; </programlisting> <para> Setting <literal>character_set_connection</literal> to <literal>x</literal> also sets <literal>collation_connection</literal> to the default collation for <literal>x</literal>. </para> <para> <literal>SET CHARACTER SET</literal> is similar but sets the connection character set and collation to be those of the default database. A <literal>SET CHARACTER SET x</literal> statement is equivalent to these three statements: </para> <programlisting> mysql> SET character_set_client = <replaceable>x</replaceable>; mysql> SET character_set_results = <replaceable>x</replaceable>; mysql> SET collation_connection = @@collation_database; </programlisting> <para> When a client connects, it sends to the server the name of the character set that it wants to use. The server sets the <literal>character_set_client</literal>, <literal>character_set_results</literal>, and <literal>character_set_connection</literal> variables to that character set. (In effect, the server performs a <literal>SET NAMES</literal> operation using the character set.) </para> <para> With the <command>mysql</command> client, it is not necessary to execute <literal>SET NAMES</literal> every time you start up if you want to use a character set different from the default. You can add the <literal>--default-character-set</literal> option setting to your <command>mysql</command> statement line, or in your option file. For example, the following option file setting changes the three character set variables set to <literal>koi8r</literal> each time you run <command>mysql</command>: </para> <programlisting> [mysql] default-character-set=koi8r </programlisting> <para> Example: Suppose that <literal>column1</literal> is defined as <literal>CHAR(5) CHARACTER SET latin2</literal>. If you do not say <literal>SET NAMES</literal> or <literal>SET CHARACTER SET</literal>, then for <literal>SELECT column1 FROM t</literal>, the server sends back all the values for <literal>column1</literal> using the character set that the client specified when it connected. On the other hand, if you say <literal>SET NAMES 'latin1'</literal> or <literal>SET CHARACTER SET latin1</literal>, then just before sending results back, the server converts the <literal>latin2</literal> values to <literal>latin1</literal>. Conversion may be lossy if there are characters that are not in both character sets. </para> <para> If you do not want the server to perform any conversion, set <literal>character_set_results</literal> to <literal>NULL</literal>: </para> <programlisting> mysql> SET character_set_results = NULL; </programlisting> </section> <section id="charset-literal"> <title id='title-charset-literal'>&title-charset-literal;</title> <para> Every character string literal has a character set and a collation, which may not be null. </para> <para> A character string literal may have an optional character set introducer and <literal>COLLATE</literal> clause: </para> <indexterm type="concept"> <primary>introducer</primary> <secondary>string literal</secondary> </indexterm> <indexterm type="concept"> <primary>string literal introducer</primary> </indexterm> <programlisting> [_<replaceable>charset_name</replaceable>]'<replaceable>string</replaceable>' [COLLATE <replaceable>collation_name</replaceable>] </programlisting> <para> Examples: <programlisting> SELECT '<replaceable>string</replaceable>'; SELECT _latin1'<replaceable>string</replaceable>'; SELECT _latin1'<replaceable>string</replaceable>' COLLATE latin1_danish_ci; </programlisting> </para> <para> For the simple statement <literal>SELECT '<replaceable>string</replaceable>'</literal>, the string has the character set and collation defined by the <literal>character_set_connection</literal> and <literal>collation_connection</literal> system variables. </para> <para> The <literal>_<replaceable>charset_name</replaceable></literal> expression is formally called an <emphasis>introducer</emphasis>. It tells the parser, ``the string that is about to follow is in character set <literal>X</literal>.'' Because this has confused people in the past, we emphasize that an introducer does not cause any conversion, it is strictly a signal that does not change the string's value. An introducer is also legal before standard hex literal and numeric hex literal notation (<literal>x'<replaceable>literal</replaceable>'</literal> and <literal>0x<replaceable>nnnn</replaceable></literal>), and before <literal>?</literal> (parameter substitution when using prepared statements within a programming language interface). </para> <para> Examples: </para> <programlisting> SELECT _latin1 x'AABBCC'; SELECT _latin1 0xAABBCC; SELECT _latin1 ?; </programlisting> <para> MySQL determines a literal's character set and collation thus: </para> <itemizedlist> <listitem><para> If both <replaceable>_X</replaceable> and <literal>COLLATE <replaceable>Y</replaceable></literal> were specified, then character set <replaceable>X</replaceable> and collation <replaceable>Y</replaceable> </para></listitem> <listitem><para> If <replaceable>_X</replaceable> is specified but <literal>COLLATE</literal> is not specified, then character set <replaceable>X</replaceable> and its default collation </para></listitem> <listitem><para> Otherwise, the character set and collation given by the <literal>character_set_connection</literal> and <literal>collation_connection</literal> system variables </para></listitem> </itemizedlist> <para> Examples: </para> <itemizedlist> <listitem><para> A string with <literal>latin1</literal> character set and <literal>latin1_german1_ci</literal> collation: </para> <programlisting> SELECT _latin1'Müller' COLLATE latin1_german1_ci; </programlisting></listitem> <listitem><para> A string with <literal>latin1</literal> character set and its default collation (that is, <literal>latin1_swedish_ci</literal>): </para> <programlisting> SELECT _latin1'Müller'; </programlisting></listitem> <listitem><para> A string with the connection default character set and collation: </para> <programlisting> SELECT 'Müller'; </programlisting></listitem> </itemizedlist> <para> Character set introducers and the <literal>COLLATE</literal> clause are implemented according to standard SQL specifications. </para> </section> <section id="charset-collate"> <title id='title-charset-collate'>&title-charset-collate;</title> <para> With the <literal>COLLATE</literal> clause, you can override whatever the default collation is for a comparison. <literal>COLLATE</literal> may be used in various parts of SQL statements. Here are some examples: </para> <itemizedlist> <listitem><para> With <literal>ORDER BY</literal>: </para> <programlisting> SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci; </programlisting></listitem> <listitem><para> With <literal>AS</literal>: </para> <programlisting> SELECT k COLLATE latin1_german2_ci AS k1 FROM t1 ORDER BY k1; </programlisting></listitem> <listitem><para> With <literal>GROUP BY</literal>: </para> <programlisting> SELECT k FROM t1 GROUP BY k COLLATE latin1_german2_ci; </programlisting></listitem> <listitem><para> With aggregate functions: </para> <programlisting> SELECT MAX(k COLLATE latin1_german2_ci) FROM t1; </programlisting></listitem> <listitem><para> With <literal>DISTINCT</literal>: </para> <programlisting> SELECT DISTINCT k COLLATE latin1_german2_ci FROM t1; </programlisting></listitem> <listitem><para> With <literal>WHERE</literal>: </para> <programlisting> SELECT * FROM t1 WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; </programlisting></listitem> <listitem><para> With <literal>HAVING</literal>: </para> <programlisting> SELECT k FROM t1 GROUP BY k HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci; </programlisting></listitem> </itemizedlist> </section> <section id="charset-collate-precedence"> <title id='title-charset-collate-precedence'>&title-charset-collate-precedence;</title> <para> The <literal>COLLATE</literal> clause has high precedence (higher than <literal>||</literal>), so the following two expressions are equivalent: </para> <programlisting> x || y COLLATE z x || (y COLLATE z) </programlisting> </section> <section id="charset-binary-op"> <title id='title-charset-binary-op'>&title-charset-binary-op;</title> <para> The <literal>BINARY</literal> operator is a shorthand for a <literal>COLLATE</literal> clause. <literal>BINARY 'x'</literal> is equivalent to <literal>'x' COLLATE y</literal>, where <literal>y</literal> is the name of the binary collation for the character set of <literal>'x'</literal>. Every character set has a binary collation. For example, the binary collation for the <literal>latin1</literal> character set is <literal>latin1_bin</literal>, so if the column <literal>a</literal> is of character set <literal>latin1</literal>, the following two statements have the same effect: </para> <programlisting> SELECT * FROM t1 ORDER BY BINARY a; SELECT * FROM t1 ORDER BY a COLLATE latin1_bin; </programlisting> </section> <section id="charset-collate-tricky"> <title id='title-charset-collate-tricky'>&title-charset-collate-tricky;</title> <para> In the great majority of queries, it is obvious what collation MySQL uses to resolve a comparison operation. For example, in the following cases, it should be clear that the collation is ``the column collation of column <literal>x</literal>'': </para> <programlisting> SELECT x FROM T ORDER BY x; SELECT x FROM T WHERE x = x; SELECT DISTINCT x FROM T; </programlisting> <para> However, when multiple operands are involved, there can be ambiguity. For example: </para> <programlisting> SELECT x FROM T WHERE x = 'Y'; </programlisting> <para> Should this query use the collation of the column <literal>x</literal>, or of the string literal <literal>'Y'</literal>? </para> <para> Standard SQL resolves such questions using what used to be called ``coercibility'' rules. The essence is: Because <literal>x</literal> and <literal>'Y'</literal> both have collations, whose collation takes precedence? It's complex, but the following rules take care of most situations: </para> <itemizedlist> <listitem><para> An explicit <literal>COLLATE</literal> clause has a coercibility of 0. (Not coercible at all.) </para></listitem> <listitem><para> A concatenation of two strings with different collations has a coercibility of 1. </para></listitem> <listitem><para> A column's collation has a coercibility of 2. </para></listitem> <listitem><para> A ``system constant'' (the string returned by functions such as <literal>USER()</literal> or <literal>VERSION()</literal>) has a coercibility of 3. </para></listitem> <listitem><para> A literal's collation has a coercibility of 4. </para></listitem> <listitem><para> <literal>NULL</literal> or an expression that is derived from <literal>NULL</literal> has a coercibility of 5. </para></listitem> </itemizedlist> <para> The preceding coercibility values are current as of MySQL 4.1.11 and 5.0.3. See the note later in this section for additional version-related information. </para> <para> Those rules resolve ambiguities thus: </para> <itemizedlist> <listitem><para> Use the collation with the lowest coercibility value. </para></listitem> <listitem><para> If both sides have the same coercibility, then it is an error if the collations aren't the same. </para></listitem> </itemizedlist> <para> Examples: </para> <informaltable> <tgroup cols="2"> <colspec colwidth="50*"/> <colspec colwidth="50*"/> <tbody> <row> <entry> <literal>column1 = 'A'</literal> </entry> <entry> Use collation of <literal>column1</literal> </entry> </row> <row> <entry> <literal>column1 = 'A' COLLATE x</literal> </entry> <entry> Use collation of <literal>'A'</literal> </entry> </row> <row> <entry> <literal>column1 COLLATE x = 'A' COLLATE y</literal> </entry> <entry> Error </entry> </row> </tbody> </tgroup> </informaltable> <para> The <literal>COERCIBILITY()</literal> function can be used to determine the coercibility of a string expression: </para> <programlisting> mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci); -> 0 mysql> SELECT COERCIBILITY(VERSION()); -> 3 mysql> SELECT COERCIBILITY('A'); -> 4 </programlisting> <para> See <xref linkend="information-functions"/>. </para> <para> Before MySQL 4.1.11 and 5.0.3, there is no system constant or ignorable coercibility. Functions such as <literal>USER()</literal> have a coercibility of 2 rather than 3, and literals have a coercibility of 3 rather than 4. </para> </section> <section id="charset-collation-charset"> <title id='title-charset-collation-charset'>&title-charset-collation-charset;</title> <para> Recall that each character set has one or more collations, and each collation is associated with one and only one character set. Therefore, the following statement causes an error message because the <literal>latin2_bin</literal> collation is not legal with the <literal>latin1</literal> character set: </para> <programlisting> mysql> SELECT _latin1 'x' COLLATE latin2_bin; ERROR 1251: COLLATION 'latin2_bin' is not valid for CHARACTER SET 'latin1' </programlisting> <para> In some cases, expressions that worked before MySQL 4.1 fail in early versions of MySQL 4.1 if you do not take character set and collation into account. For example, before 4.1, this statement works as is: </para> <programlisting> mysql> SELECT SUBSTRING_INDEX(USER(),'@',1); +-------------------------------+ | SUBSTRING_INDEX(USER(),'@',1) | +-------------------------------+ | root | +-------------------------------+ </programlisting> <para> The statement also works as is in MySQL 4.1 as of 4.1.8: In MySQL 4.1, usernames are stored using the <literal>utf8</literal> character set (see <xref linkend="charset-metadata"/>). The literal string <literal>'@'</literal> has the server character set (<literal>latin1</literal> by default). Although the character sets are different, MySQL can coerce the <literal>latin1</literal> string to the character set (and collation) of <literal>USER()</literal> without data loss. It does so, performs the substring operation, and returns a result that has a character set of <literal>utf8</literal>. </para> <para> However, in versions of MySQL 4.1 before 4.1.8, the statement fails: </para> <programlisting> mysql> SELECT SUBSTRING_INDEX(USER(),'@',1); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'substr_index' </programlisting> <para> This happens because the automatic character set conversion of <literal>'@'</literal> does not occur and the string operands have different character sets (and thus different collations): </para> <programlisting> mysql> SELECT COLLATION(USER()), COLLATION('@'); +-------------------+-------------------+ | COLLATION(USER()) | COLLATION('@') | +-------------------+-------------------+ | utf8_general_ci | latin1_swedish_ci | +-------------------+-------------------+ </programlisting> <para> One way to deal with this is to upgrade to MySQL 4.1.8 or later. If that is not possible, you can tell MySQL to interpret the literal string as <literal>utf8</literal>: </para> <programlisting> mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1); +------------------------------------+ | SUBSTRING_INDEX(USER(),_utf8'@',1) | +------------------------------------+ | root | +------------------------------------+ </programlisting> <para> Another way is to change the connection character set and collation to <literal>utf8</literal>. You can do that with <literal>SET NAMES 'utf8'</literal> or by setting the <literal>character_set_connection</literal> and <literal>collation_connection</literal> system variables directly. </para> </section> <section id="charset-collation-effect"> <title id='title-charset-collation-effect'>&title-charset-collation-effect;</title> <para> Suppose that column <literal>X</literal> in table <literal>T</literal> has these <literal>latin1</literal> column values: </para> <programlisting> Muffler Müller MX Systems MySQL </programlisting> <para> And suppose that the column values are retrieved using the following statement: </para> <programlisting> SELECT X FROM T ORDER BY X COLLATE <replaceable>collation_name</replaceable>; </programlisting> <para> The resulting order of the values for different collations is shown in this table: </para> <informaltable> <tgroup cols="3"> <colspec colwidth="30*"/> <colspec colwidth="30*"/> <colspec colwidth="30*"/> <tbody> <row> <entry> <literal>latin1_swedish_ci</literal> </entry> <entry> <literal>latin1_german1_ci</literal> </entry> <entry> <literal>latin1_german2_ci</literal> </entry> </row> <row> <entry> Muffler </entry> <entry> Muffler </entry> <entry> Müller </entry> </row> <row> <entry> MX Systems </entry> <entry> Müller </entry> <entry> Muffler </entry> </row> <row> <entry> Müller </entry> <entry> MX Systems </entry> <entry> MX Systems </entry> </row> <row> <entry> MySQL </entry> <entry> MySQL </entry> <entry> MySQL </entry> </row> </tbody> </tgroup> </informaltable> <para> The table is an example that shows what the effect would be if we used different collations in an <literal>ORDER BY</literal> clause. The character that causes the different sort orders in this example is the U with two dots over it, which the Germans call U-umlaut, but we'll call it U-diaeresis. </para> <itemizedlist> <listitem><para> The first column shows the result of the <literal>SELECT</literal> using the Swedish/Finnish collating rule, which says that U-diaeresis sorts with Y. </para></listitem> <listitem><para> The second column shows the result of the <literal>SELECT</literal> using the German DIN-1 rule, which says that U-diaeresis sorts with U. </para></listitem> <listitem><para> The third column shows the result of the <literal>SELECT</literal> using the German DIN-2 rule, which says that U-diaeresis sorts with UE. </para></listitem> </itemizedlist> <para> Three different collations, three different results. That's what MySQL is here to handle. By using the appropriate collation, you can choose the sort order you want. </para> </section> </section> <section id="charset-operations"> <title id='title-charset-operations'>&title-charset-operations;</title> <para> This section describes operations that take character set information into account as of MySQL 4.1. </para> <section id="charset-result"> <title id='title-charset-result'>&title-charset-result;</title> <para> MySQL has many operators and functions that return a string. This section answers the question: What is the character set and collation of such a string? </para> <para> For simple functions that take string input and return a string result as output, the output's character set and collation are the same as those of the principal input value. For example, <literal>UPPER(<replaceable>X</replaceable>)</literal> returns a string whose character string and collation are the same as that of <replaceable>X</replaceable>. The same applies for <literal>INSTR()</literal>, <literal>LCASE()</literal>, <literal>LOWER()</literal>, <literal>LTRIM()</literal>, <literal>MID()</literal>, <literal>REPEAT()</literal>, <literal>REPLACE()</literal>, <literal>REVERSE()</literal>, <literal>RIGHT()</literal>, <literal>RPAD()</literal>, <literal>RTRIM()</literal>, <literal>SOUNDEX()</literal>, <literal>SUBSTRING()</literal>, <literal>TRIM()</literal>, <literal>UCASE()</literal>, and <literal>UPPER()</literal>. (Also note: The <literal>REPLACE()</literal> function, unlike all other functions, ignores the collation of the string input and performs a case-insensitive comparison every time.) </para> <para> For operations that combine multiple string inputs and return a single string output, the ``aggregation rules'' of standard SQL apply: </para> <itemizedlist> <listitem><para> If an explicit <literal>COLLATE X</literal> occurs, then use <literal>X</literal> </para></listitem> <listitem><para> If an explicit <literal>COLLATE X</literal> and <literal>COLLATE Y</literal> occur, then error </para></listitem> <listitem><para> Otherwise, if all collations are <literal>X</literal>, then use <literal>X</literal> </para></listitem> <listitem><para> Otherwise, the result has no collation </para></listitem> </itemizedlist> <para> For example, with <literal>CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END</literal>, the resultant collation is <literal>X</literal>. The same applies for <literal>CASE</literal>, <literal>UNION</literal>, <literal>||</literal>, <literal>CONCAT()</literal>, <literal>ELT()</literal>, <literal>GREATEST()</literal>, <literal>IF()</literal>, and <literal>LEAST()</literal>. </para> <para> For operations that convert to character data, the character set and collation of the strings that result from the operations are defined by the <literal>character_set_connection</literal> and <literal>collation_connection</literal> system variables. This applies for <literal>CAST()</literal>, <literal>CHAR()</literal>, <literal>CONV()</literal>, <literal>FORMAT()</literal>, <literal>HEX()</literal>, and <literal>SPACE()</literal>. </para> </section> <section id="charset-convert"> <title id='title-charset-convert'>&title-charset-convert;</title> <para> <literal>CONVERT()</literal> provides a way to convert data between different character sets. The syntax is: </para> <programlisting> CONVERT(<replaceable>expr</replaceable> USING <replaceable>transcoding_name</replaceable>) </programlisting> <para> In MySQL, transcoding names are the same as the corresponding character set names. </para> <para> Examples: </para> <programlisting> SELECT CONVERT(_latin1'Müller' USING utf8); INSERT INTO utf8table (utf8column) SELECT CONVERT(latin1field USING utf8) FROM latin1table; </programlisting> <para> <literal>CONVERT(... USING ...)</literal> is implemented according to the standard SQL specification. </para> <para> In <literal>TRADITIONAL</literal> SQL mode, if you convert a ``zero'' date string to a date, <literal>CONVERT()</literal> returns <literal>NULL</literal>. MySQL 5.0.4 and above also produces a warning. </para> </section> <section id="charset-cast"> <title id='title-charset-cast'>&title-charset-cast;</title> <para> You may also use <literal>CAST()</literal> to convert a string to a different character set. The syntax is: </para> <programlisting> CAST(<replaceable>character_string</replaceable> AS <replaceable>character_data_type</replaceable> CHARACTER SET <replaceable>charset_name</replaceable>) </programlisting> <para> Example: </para> <programlisting> SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8); </programlisting> <para> If you use <literal>CAST()</literal> without specifying <literal>CHARACTER SET</literal>, the resulting character set and collation are defined by the <literal>character_set_connection</literal> and <literal>collation_connection</literal> system variables. If you use <literal>CAST()</literal> with <literal>CHARACTER SET X</literal>, then the resulting character set and collation are <literal>X</literal> and the default collation of <literal>X</literal>. </para> <para> You may not use a <literal>COLLATE</literal> clause inside a <literal>CAST()</literal>, but you may use it outside. That is, <literal>CAST(... COLLATE ...)</literal> is illegal, but <literal>CAST(...) COLLATE ...</literal> is legal. </para> <para> Example: </para> <programlisting> SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin; </programlisting> <para> In <literal>TRADITIONAL</literal> SQL mode, if you convert a ``zero'' date string to a date, <literal>CAST()</literal> returns <literal>NULL</literal>. MySQL 5.0.4 and above also produces a warning. </para> </section> <section id="charset-show"> <title id='title-charset-show'>&title-charset-show;</title> <para> Several <literal>SHOW</literal> statements are new or modified in MySQL 4.1 to provide additional character set information. <literal>SHOW CHARACTER SET</literal>, <literal>SHOW COLLATION</literal>, and <literal>SHOW CREATE DATABASE</literal> are new. <literal>SHOW CREATE TABLE</literal> and <literal>SHOW COLUMNS</literal> are modified. </para> <para> The <literal>SHOW CHARACTER SET</literal> command shows all available character sets. It takes an optional <literal>LIKE</literal> clause that indicates which character set names to match. For example: </para> <programlisting> mysql> SHOW CHARACTER SET LIKE 'latin%'; +---------+-----------------------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+-----------------------------+-------------------+--------+ | latin1 | ISO 8859-1 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | +---------+-----------------------------+-------------------+--------+ </programlisting> <para> See <xref linkend="show-character-set"/>. </para> <para> The output from <literal>SHOW COLLATION</literal> includes all available character sets. It takes an optional <literal>LIKE</literal> clause that indicates which collation names to match. For example: </para> <programlisting> mysql> SHOW COLLATION LIKE 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 0 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +-------------------+---------+----+---------+----------+---------+ </programlisting> <para> See <xref linkend="show-collation"/>. </para> <para> <literal>SHOW CREATE DATABASE</literal> displays the <literal>CREATE DATABASE</literal> statement that creates a given database. The result includes all database options. <literal>DEFAULT CHARACTER SET</literal> and <literal>COLLATE</literal> are supported. All database options are stored in a text file named <filename>db.opt</filename> that can be found in the database directory. </para> <programlisting> mysql> SHOW CREATE DATABASE a\G *************************** 1. row *************************** Database: a Create Database: CREATE DATABASE `a` /*!40100 DEFAULT CHARACTER SET macce */ </programlisting> <para> See <xref linkend="show-create-database"/>. </para> <para> <literal>SHOW CREATE TABLE</literal> is similar, but displays the <literal>CREATE TABLE</literal> statement to create a given table. The column definitions indicate any character set specifications, and the table options include character set information. </para> <para> See <xref linkend="show-create-table"/>. </para> <para> The <literal>SHOW COLUMNS</literal> statement displays the collations of a table's columns when invoked as <literal>SHOW FULL COLUMNS</literal>. Columns with <literal>CHAR</literal>, <literal>VARCHAR</literal>, or <literal>TEXT</literal> data types have non-<literal>NULL</literal> collations. Numeric and other non-character types have <literal>NULL</literal> collations. For example: </para> <programlisting> mysql> SHOW FULL COLUMNS FROM t; +-------+---------+------------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------+---------+------------+------+-----+---------+-------+ | a | char(1) | latin1_bin | YES | | NULL | | | b | int(11) | NULL | YES | | NULL | | +-------+---------+------------+------+-----+---------+-------+ </programlisting> <para> The character set is not part of the display. (The character set name is implied by the collation name.) </para> <para> See <xref linkend="show-columns"/>. </para> </section> </section> <section id="charset-unicode"> <title id='title-charset-unicode'>&title-charset-unicode;</title> <para> As of MySQL version 4.1, there are two new character sets for storing Unicode data: </para> <itemizedlist> <listitem><para> <literal>ucs2</literal>, the UCS-2 Unicode character set. </para></listitem> <listitem><para> <literal>utf8</literal>, the UTF8 encoding of the Unicode character set. </para></listitem> </itemizedlist> <para> In UCS-2 (binary Unicode representation), every character is represented by a two-byte Unicode code with the most significant byte first. For example: "LATIN CAPITAL LETTER A" has the code 0x0041 and it's stored as a two-byte sequence: 0x00 0x41. "CYRILLIC SMALL LETTER YERU" (Unicode 0x044B) is stored as a two-byte sequence: 0x04 0x4B. For Unicode characters and their codes, please refer to the <ulink url="http://www.unicode.org/">Unicode Home Page</ulink>. </para> <para> A temporary restriction is that UCS-2 cannot yet be used as a client character set. That means that <literal>SET NAMES 'ucs2'</literal> does not work. </para> <para> The UTF8 character set (transform Unicode representation) is an alternative way to store Unicode data. It is implemented according to RFC 3629. The idea of the UTF8 character set is that various Unicode characters are encoded using byte sequences of different lengths: </para> <itemizedlist> <listitem><para> Basic Latin letters, digits, and punctuation signs use one byte. </para></listitem> <listitem><para> Most European and Middle East script letters fit into a two-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others. </para></listitem> <listitem><para> Korean, Chinese, and Japanese ideographs use three-byte sequences. </para></listitem> </itemizedlist> <para> RFC 3629 describes encoding sequences that take from one to four bytes. Currently, MySQL UTF8 support does not include four-byte sequences. (An older standard for UTF8 encoding is given by RFC 2279, which describes UTF8 sequences that take from one to six bytes. RFC 3629 renders RFC 2279 obsolete and sequences with five and six bytes now are not used.) </para> <para> Tip: To save space with UTF8, use <literal>VARCHAR</literal> instead of <literal>CHAR</literal>. Otherwise, MySQL has to reserve 30 bytes for a <literal>CHAR(10) CHARACTER SET utf8</literal> column, because that's the maximum possible length. </para> </section> <section id="charset-metadata"> <title id='title-charset-metadata'>&title-charset-metadata;</title> <para> The metadata is the data about the data. Anything that describes the database, as opposed to being the contents of the database, is metadata. Thus column names, database names, usernames, version names, and most of the string results from <literal>SHOW</literal> are metadata. </para> <para> Representation of metadata must satisfy these requirements: </para> <itemizedlist> <listitem><para> All metadata must be in the same character set. Otherwise, <literal>SHOW</literal> wouldn't work properly because different rows in the same column would be in different character sets. </para></listitem> <listitem><para> Metadata must include all characters in all languages. Otherwise, users wouldn't be able to name columns and tables in their own languages. </para></listitem> </itemizedlist> <para> In order to satisfy both requirements, MySQL stores metadata in a Unicode character set, namely UTF8. This does not cause any disruption if you never use accented characters. But if you do, you should be aware that metadata is in UTF8. </para> <para> This means that the <literal>USER()</literal>, <literal>CURRENT_USER()</literal>, <literal>DATABASE()</literal>, and <literal>VERSION()</literal> functions have the UTF8 character set by default, as do synonyms such as <literal>SESSION_USER()</literal> and <literal>SYSTEM_USER()</literal>. </para> <para> The server sets the <literal>character_set_system</literal> system variable to the name of the metadata character set: </para> <programlisting> mysql> SHOW VARIABLES LIKE 'character_set_system'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | character_set_system | utf8 | +----------------------+-------+ </programlisting> <para> Storage of metadata using Unicode does <emphasis>not</emphasis> mean that the headers of columns and the results of <literal>DESCRIBE</literal> functions are in the <literal>character_set_system</literal> character set by default. When you say <literal>SELECT column1 FROM t</literal>, the name <literal>column1</literal> itself is returned from the server to the client in the character set as determined by the <literal>SET NAMES</literal> statement. More specifically, the character set used is determined by the value of the <literal>character_set_results</literal> system variable. If this variable is set to <literal>NULL</literal>, no conversion is performed and the server returns metadata using its original character set (the set indicated by <literal>character_set_system</literal>). </para> <para> If you want the server to pass metadata results back in a non-UTF8 character set, then use <literal>SET NAMES</literal> to force the server to perform character set conversion (see <xref linkend="charset-connection"/>), or else set the client to do the conversion. It is always more efficient to set the client to do the conversion, but this option is not available for many clients until late in the MySQL 4.x product cycle. </para> <para> If you are just using, for example, the <literal>USER()</literal> function for comparison or assignment within a single statement, don't worry. MySQL does some automatic conversion for you. </para> <programlisting> SELECT * FROM Table1 WHERE USER() = latin1_column; </programlisting> <para> This works because the contents of <literal>latin1_column</literal> are automatically converted to UTF8 before the comparison. </para> <programlisting> INSERT INTO Table1 (latin1_column) SELECT USER(); </programlisting> <para> This works because the contents of <literal>USER()</literal> are automatically converted to <literal>latin1</literal> before the assignment. Automatic conversion is not fully implemented yet, but should work correctly in a later version. </para> <para> Although automatic conversion is not in the SQL standard, the SQL standard document does say that every character set is (in terms of supported characters) a ``subset'' of Unicode. Since it is a well-known principle that ``what applies to a superset can apply to a subset,'' we believe that a collation for Unicode can apply for comparisons with non-Unicode strings. </para> <!-- VERSION 4.1.1 NOTE: The @file{errmsg.txt} files all be in UTF8 --> <!-- after this point. Conversion to the client character set is --> <!-- automatic, as for metadata. Also: We may change the default --> <!-- behavior for passing back result set metadata in the near --> <!-- future. --> </section> <section id="charset-compatibility"> <title id='title-charset-compatibility'>&title-charset-compatibility;</title> <para> For MaxDB compatibility these two statements are the same: </para> <programlisting> CREATE TABLE t1 (f1 CHAR(<replaceable>n</replaceable>) UNICODE); CREATE TABLE t1 (f1 CHAR(<replaceable>n</replaceable>) CHARACTER SET ucs2); </programlisting> </section> <section id="charset-config-file"> <title id='title-charset-config-file'>&title-charset-config-file;</title> <para> In MySQL 4.1, character set configuration is stored in XML files, one file per character set. In previous versions, this information was stored in <filename>.conf</filename> files. </para> </section> <section id="charset-national"> <title id='title-charset-national'>&title-charset-national;</title> <para> Before MySQL 4.1, <literal>NCHAR</literal> and <literal>CHAR</literal> were synonymous. ANSI defines <literal>NCHAR</literal> or <literal>NATIONAL CHAR</literal> as a way to indicate that a <literal>CHAR</literal> column should use some predefined character set. MySQL 4.1 and up uses <literal>utf8</literal> as that predefined character set. For example, these column type declarations are equivalent: </para> <programlisting> CHAR(10) CHARACTER SET utf8 NATIONAL CHARACTER(10) NCHAR(10) </programlisting> <para> As are these: </para> <programlisting> VARCHAR(10) CHARACTER SET utf8 NATIONAL VARCHAR(10) NCHAR VARCHAR(10) NATIONAL CHARACTER VARYING(10) NATIONAL CHAR VARYING(10) </programlisting> <para> You can use <literal>N'<replaceable>literal</replaceable>'</literal> to create a string in the national character set. These two statements are equivalent: </para> <programlisting> SELECT N'some text'; SELECT _utf8'some text'; </programlisting> </section> <section id="charset-upgrading"> <title id='title-charset-upgrading'>&title-charset-upgrading;</title> <para> What about upgrading from older versions of MySQL? MySQL 4.1 is almost upward compatible with MySQL 4.0 and earlier for the simple reason that almost all the features are new, so there's nothing in earlier versions to conflict with. However, there are some differences and a few things to be aware of. </para> <para> Most important: The ``MySQL 4.0 character set'' has the properties of both ``MySQL 4.1 character sets'' and ``MySQL 4.1 collations.'' You have to unlearn this. Henceforth, we does not bundle character set/collation properties in the same conglomerate object. </para> <para> There is a special treatment of national character sets in MySQL 4.1. <literal>NCHAR</literal> is not the same as <literal>CHAR</literal>, and <literal>N'...'</literal> literals are not the same as <literal>'...'</literal> literals. </para> <para> Finally, there is a different file format for storing information about character sets and collations. Make sure that you have reinstalled the <filename>/share/mysql/charsets/</filename> directory containing the new configuration files. </para> <para> If you want to start <command>mysqld</command> from a 4.1.x distribution with data created by MySQL 4.0, you should start the server with the same character set and collation. In this case, you won't need to reindex your data. </para> <para> There are two ways to do so: </para> <programlisting> shell> ./configure --with-charset=... --with-collation=... shell> ./mysqld --default-character-set=... --default-collation=... </programlisting> <para> If you used <command>mysqld</command> with, for example, the MySQL 4.0 <literal>danish</literal> character set, you should use the <literal>latin1</literal> character set and the <literal>latin1_danish_ci</literal> collation: </para> <programlisting> shell> ./configure --with-charset=latin1 \ --with-collation=latin1_danish_ci shell> ./mysqld --default-character-set=latin1 \ --default-collation=latin1_danish_ci </programlisting> <para> Use the table shown in <xref linkend="charset-map"/> to find old 4.0 character set names and their 4.1 character set/collation pair equivalents. </para> <para> If you have non-<literal>latin1</literal> data stored in a 4.0 <literal>latin1</literal> table and want to convert the table column definitions to reflect the actual character set of the data, use the instructions in <xref linkend="charset-conversion"/>. </para> <section id="charset-map"> <title id='title-charset-map'>&title-charset-map;</title> <informaltable> <tgroup cols="4"> <colspec colwidth="10*"/> <colspec colwidth="25*"/> <colspec colwidth="25*"/> <colspec colwidth="35*"/> <tbody> <row> <entry> <emphasis role="bold">ID</emphasis> </entry> <entry> <emphasis role="bold">4.0 Character Set</emphasis> </entry> <entry> <emphasis role="bold">4.1 Character Set</emphasis> </entry> <entry> <emphasis role="bold">4.1 Collation</emphasis> </entry> </row> <row> <entry> 1 </entry> <entry> <literal>big5</literal> </entry> <entry> <literal>big5</literal> </entry> <entry> <literal>big5_chinese_ci</literal> </entry> </row> <row> <entry> 2 </entry> <entry> <literal>czech</literal> </entry> <entry> <literal>latin2</literal> </entry> <entry> <literal>latin2_czech_ci</literal> </entry> </row> <row> <entry> 3 </entry> <entry> <literal>dec8</literal> </entry> <entry> <literal>dec8</literal> </entry> <entry> <literal>dec8_swedish_ci</literal> </entry> </row> <row> <entry> 4 </entry> <entry> <literal>dos</literal> </entry> <entry> <literal>cp850</literal> </entry> <entry> <literal>cp850_general_ci</literal> </entry> </row> <row> <entry> 5 </entry> <entry> <literal>german1</literal> </entry> <entry> <literal>latin1</literal> </entry> <entry> <literal>latin1_german1_ci</literal> </entry> </row> <row> <entry> 6 </entry> <entry> <literal>hp8</literal> </entry> <entry> <literal>hp8</literal> </entry> <entry> <literal>hp8_english_ci</literal> </entry> </row> <row> <entry> 7 </entry> <entry> <literal>koi8_ru</literal> </entry> <entry> <literal>koi8r</literal> </entry> <entry> <literal>koi8r_general_ci</literal> </entry> </row> <row> <entry> 8 </entry> <entry> <literal>latin1</literal> </entry> <entry> <literal>latin1</literal> </entry> <entry> <literal>latin1_swedish_ci</literal> </entry> </row> <row> <entry> 9 </entry> <entry> <literal>latin2</literal> </entry> <entry> <literal>latin2</literal> </entry> <entry> <literal>latin2_general_ci</literal> </entry> </row> <row> <entry> 10 </entry> <entry> <literal>swe7</literal> </entry> <entry> <literal>swe7</literal> </entry> <entry> <literal>swe7_swedish_ci</literal> </entry> </row> <row> <entry> 11 </entry> <entry> <literal>usa7</literal> </entry> <entry> <literal>ascii</literal> </entry> <entry> <literal>ascii_general_ci</literal> </entry> </row> <row> <entry> 12 </entry> <entry> <literal>ujis</literal> </entry> <entry> <literal>ujis</literal> </entry> <entry> <literal>ujis_japanese_ci</literal> </entry> </row> <row> <entry> 13 </entry> <entry> <literal>sjis</literal> </entry> <entry> <literal>sjis</literal> </entry> <entry> <literal>sjis_japanese_ci</literal> </entry> </row> <row> <entry> 14 </entry> <entry> <literal>cp1251</literal> </entry> <entry> <literal>cp1251</literal> </entry> <entry> <literal>cp1251_bulgarian_ci</literal> </entry> </row> <row> <entry> 15 </entry> <entry> <literal>danish</literal> </entry> <entry> <literal>latin1</literal> </entry> <entry> <literal>latin1_danish_ci</literal> </entry> </row> <row> <entry> 16 </entry> <entry> <literal>hebrew</literal> </entry> <entry> <literal>hebrew</literal> </entry> <entry> <literal>hebrew_general_ci</literal> </entry> </row> <row> <entry> 17 </entry> <entry> <literal>win1251</literal> </entry> <entry> <literal>(removed)</literal> </entry> <entry> <literal>(removed)</literal> </entry> </row> <row> <entry> 18 </entry> <entry> <literal>tis620</literal> </entry> <entry> <literal>tis620</literal> </entry> <entry> <literal>tis620_thai_ci</literal> </entry> </row> <row> <entry> 19 </entry> <entry> <literal>euc_kr</literal> </entry> <entry> <literal>euckr</literal> </entry> <entry> <literal>euckr_korean_ci</literal> </entry> </row> <row> <entry> 20 </entry> <entry> <literal>estonia</literal> </entry> <entry> <literal>latin7</literal> </entry> <entry> <literal>latin7_estonian_ci</literal> </entry> </row> <row> <entry> 21 </entry> <entry> <literal>hungarian</literal> </entry> <entry> <literal>latin2</literal> </entry> <entry> <literal>latin2_hungarian_ci</literal> </entry> </row> <row> <entry> 22 </entry> <entry> <literal>koi8_ukr</literal> </entry> <entry> <literal>koi8u</literal> </entry> <entry> <literal>koi8u_ukrainian_ci</literal> </entry> </row> <row> <entry> 23 </entry> <entry> <literal>win1251ukr</literal> </entry> <entry> <literal>cp1251</literal> </entry> <entry> <literal>cp1251_ukrainian_ci</literal> </entry> </row> <row> <entry> 24 </entry> <entry> <literal>gb2312</literal> </entry> <entry> <literal>gb2312</literal> </entry> <entry> <literal>gb2312_chinese_ci</literal> </entry> </row> <row> <entry> 25 </entry> <entry> <literal>greek</literal> </entry> <entry> <literal>greek</literal> </entry> <entry> <literal>greek_general_ci</literal> </entry> </row> <row> <entry> 26 </entry> <entry> <literal>win1250</literal> </entry> <entry> <literal>cp1250</literal> </entry> <entry> <literal>cp1250_general_ci</literal> </entry> </row> <row> <entry> 27 </entry> <entry> <literal>croat</literal> </entry> <entry> <literal>latin2</literal> </entry> <entry> <literal>latin2_croatian_ci</literal> </entry> </row> <row> <entry> 28 </entry> <entry> <literal>gbk</literal> </entry> <entry> <literal>gbk</literal> </entry> <entry> <literal>gbk_chinese_ci</literal> </entry> </row> <row> <entry> 29 </entry> <entry> <literal>cp1257</literal> </entry> <entry> <literal>cp1257</literal> </entry> <entry> <literal>cp1257_lithuanian_ci</literal> </entry> </row> <row> <entry> 30 </entry> <entry> <literal>latin5</literal> </entry> <entry> <literal>latin5</literal> </entry> <entry> <literal>latin5_turkish_ci</literal> </entry> </row> <row> <entry> 31 </entry> <entry> <literal>latin1_de</literal> </entry> <entry> <literal>latin1</literal> </entry> <entry> <literal>latin1_german2_ci</literal> </entry> </row> </tbody> </tgroup> </informaltable> </section> <section id="charset-conversion"> <title id='title-charset-conversion'>&title-charset-conversion;</title> <para> Normally, the server runs using the <literal>latin1</literal> character set by default. If you have been storing column data that actually is in some other character set that the 4.1 server supports directly, you can convert the column. However, you should avoid trying to convert directly from <literal>latin1</literal> to the "real" character set. This may result in data loss. Instead, convert the column to a binary column type, and then from the binary type to a non-binary type with the desired character set. Conversion to and from binary involves no attempt at character value conversion and preserves your data intact. For example, suppose that you have a 4.0 table with three columns that are used to store values represented in <literal>latin1</literal>, <literal>latin2</literal>, and <literal>utf8</literal>: </para> <programlisting> CREATE TABLE t ( latin1_col CHAR(50), latin2_col CHAR(100), utf8_col CHAR(150) ); </programlisting> <para> For MySQL 4.1, you want to convert this table to leave <literal>latin1_col</literal> alone but change the <literal>latin2_col</literal> and <literal>utf8_col</literal> columns to have character sets of <literal>latin2</literal> and <literal>utf8</literal>. Before upgrading to 4.1, back up your table, then convert the columns as follows: </para> <programlisting> ALTER TABLE t MODIFY latin2_col BINARY(100); ALTER TABLE t MODIFY utf8_col BINARY(150); </programlisting> <para> Then, after upgrading to 4.1, complete the conversion by issuing these statements: </para> <programlisting> ALTER TABLE t MODIFY latin2_col CHAR(100) CHARACTER SET latin2; ALTER TABLE t MODIFY utf8_col CHAR(150) CHARACTER SET utf8; </programlisting> <para> The first two statements ``remove'' the character set information from the <literal>latin2_col</literal> and <literal>utf8_col</literal> columns. The second two statements assign the proper character sets to the two columns. </para> <para> If you like, you can combine the to-binary conversions and from-binary conversions into single statements. In MySQL 4.0, do this: </para> <programlisting> ALTER TABLE t MODIFY latin2_col BINARY(100), MODIFY utf8_col BINARY(150); </programlisting> <para> After upgrading to 4.1, do this: </para> <programlisting> ALTER TABLE t MODIFY latin2_col CHAR(100) CHARACTER SET latin2, MODIFY utf8_col CHAR(150) CHARACTER SET utf8; </programlisting> <para> If you can ensure that the tables will not otherwise be modified before you perform the character set conversion, you can issue all of the <literal>ALTER TABLE</literal> statements after upgrading to MySQL 4.1. </para> <para> If you specified attributes when creating a column initially, you should also specify them when altering the table with <literal>ALTER TABLE</literal>. For example, if you specified <literal>NOT NULL</literal> and an explicit <literal>DEFAULT</literal> value, you should also provide them in the <literal>ALTER TABLE</literal> statement. Otherwise, the resulting column definition will not include those attributes. </para> </section> </section> <section id="charset-charsets"> <title id='title-charset-charsets'>&title-charset-charsets;</title> <para> Here is an annotated list of character sets and collations that MySQL supports. Because options and installation settings differ, some sites might not have all items listed, and some sites might have items not listed. </para> <para> MySQL supports 70+ collations for 30+ character sets. The character sets and their default collations are displayed by the <literal>SHOW CHARACTER SET</literal> statement. (The output actually includes another column that is not shown so that the example fits better on the page.) </para> <!-- NOTE: If you rerun the following command, trim off the Maxlen column. --> <!-- Otherwise, the lines are too long to fit nicely on the page. --> <!-- (colrm 65 works well currently) --> <programlisting> mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+ | Charset | Description | Default collation | +----------+-----------------------------+---------------------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | | dec8 | DEC West European | dec8_swedish_ci | | cp850 | DOS West European | cp850_general_ci | | hp8 | HP West European | hp8_english_ci | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | | latin1 | ISO 8859-1 West European | latin1_swedish_ci | | latin2 | ISO 8859-2 Central European | latin2_general_ci | | swe7 | 7bit Swedish | swe7_swedish_ci | | ascii | US ASCII | ascii_general_ci | | ujis | EUC-JP Japanese | ujis_japanese_ci | | sjis | Shift-JIS Japanese | sjis_japanese_ci | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | | tis620 | TIS620 Thai | tis620_thai_ci | | euckr | EUC-KR Korean | euckr_korean_ci | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | | greek | ISO 8859-7 Greek | greek_general_ci | | cp1250 | Windows Central European | cp1250_general_ci | | gbk | GBK Simplified Chinese | gbk_chinese_ci | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | | utf8 | UTF-8 Unicode | utf8_general_ci | | ucs2 | UCS-2 Unicode | ucs2_general_ci | | cp866 | DOS Russian | cp866_general_ci | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | | macce | Mac Central European | macce_general_ci | | macroman | Mac West European | macroman_general_ci | | cp852 | DOS Central European | cp852_general_ci | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | | cp1251 | Windows Cyrillic | cp1251_general_ci | | cp1256 | Windows Arabic | cp1256_general_ci | | cp1257 | Windows Baltic | cp1257_general_ci | | binary | Binary pseudo charset | binary | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | +----------+-----------------------------+---------------------+ </programlisting> <section id="charset-unicode-sets"> <title id='title-charset-unicode-sets'>&title-charset-unicode-sets;</title> <para> MySQL has two Unicode character sets. You can store texts in about 650 languages using these character sets. We have added several collations for these two new sets, with more to come. </para> <itemizedlist> <listitem><para> <literal>ucs2</literal> (UCS-2 Unicode) collations: </para> <itemizedlist> <listitem><para> <literal>ucs2_bin</literal> </para></listitem> <listitem><para> <literal>ucs2_czech_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_danish_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_estonian_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_general_ci</literal> (default) </para></listitem> <listitem><para> <literal>ucs2_icelandic_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_latvian_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_lithuanian_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_persian_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_polish_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_roman_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_romanian_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_slovak_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_slovenian_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_spanish2_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_spanish_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_swedish_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_turkish_ci</literal> </para></listitem> <listitem><para> <literal>ucs2_unicode_ci</literal> </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>utf8</literal> (UTF-8 Unicode) collations: </para> <itemizedlist> <listitem><para> <literal>utf8_bin</literal> </para></listitem> <listitem><para> <literal>utf8_czech_ci</literal> </para></listitem> <listitem><para> <literal>utf8_danish_ci</literal> </para></listitem> <listitem><para> <literal>utf8_estonian_ci</literal> </para></listitem> <listitem><para> <literal>utf8_general_ci</literal> (default) </para></listitem> <listitem><para> <literal>utf8_icelandic_ci</literal> </para></listitem> <listitem><para> <literal>utf8_latvian_ci</literal> </para></listitem> <listitem><para> <literal>utf8_lithuanian_ci</literal> </para></listitem> <listitem><para> <literal>utf8_persian_ci</literal> </para></listitem> <listitem><para> <literal>utf8_polish_ci</literal> </para></listitem> <listitem><para> <literal>utf8_roman_ci</literal> </para></listitem> <listitem><para> <literal>utf8_romanian_ci</literal> </para></listitem> <listitem><para> <literal>utf8_slovak_ci</literal> </para></listitem> <listitem><para> <literal>utf8_slovenian_ci</literal> </para></listitem> <listitem><para> <literal>utf8_spanish2_ci</literal> </para></listitem> <listitem><para> <literal>utf8_spanish_ci</literal> </para></listitem> <listitem><para> <literal>utf8_swedish_ci</literal> </para></listitem> <listitem><para> <literal>utf8_turkish_ci</literal> </para></listitem> <listitem><para> <literal>utf8_unicode_ci</literal> </para></listitem> </itemizedlist></listitem> </itemizedlist> <indexterm type="type"> <primary>Unicode Collation Algorithm</primary> </indexterm> <para> The <literal>utf8_unicode_ci</literal> collation is implemented according to the Unicode Collation Algorithm (UCA) described at <ulink url="http://www.unicode.org/reports/tr10/">http://www.unicode.org/reports/tr10/</ulink>. The collation uses the default UCA weight keys: <ulink url="http://www.unicode.org/Public/UCA/latest/allkeys.txt">http://www.unicode.org/Public/UCA/latest/allkeys.txt</ulink>. (The following discussion uses <literal>utf8_unicode_ci</literal>, but it is also true for <literal>ucs2_unicode_ci</literal>.) </para> <para> Currently, the <literal>utf8_unicode_ci</literal> collation has only partial support for the Unicode Collation Algorithm. Some characters are not supported yet. Also, combining marks are not fully supported. This affects primarily Vietnamese and some minority languages in Russia such as Udmurt, Tatar, Bashkir, and Mari. </para> <para> The most significant feature in <literal>utf8_unicode_ci</literal> is that it supports expansions, that is, when one character compares equal to several characters. For example, in German and some other languages '<literal>ß</literal>' is equal to '<literal>ss</literal>'. </para> <para> <literal>utf8_general_ci</literal> is a legacy collation that does not support expansions. It can only compare characters one to one. This means that comparisons for the <literal>utf8_general_ci</literal> collation are faster, but slightly less correct, than comparisons for <literal>utf8_unicode_ci</literal>). </para> <para> For example, the following equalities hold in both <literal>utf8_general_ci</literal> and <literal>utf8_unicode_ci</literal>: </para> <programlisting> Ä = A Ö = O Ü = U </programlisting> <para> A difference between the collations is that this is true for <literal>utf8_general_ci</literal>: </para> <programlisting> ß = s </programlisting> <para> Whereas this is true for <literal>utf8_unicode_ci</literal>: </para> <programlisting> ß = ss </programlisting> <para> Language-specific collations for the utf8 character set are implemented only if the ordering with utf8_unicode_ci does not work well for a language. For example, <literal>utf8_unicode_ci</literal> works fine for German and French, so there is no need to create special <literal>utf8</literal> collations for these two languages. </para> <para> <literal>utf8_general_ci</literal> is fine for both German and French, too, with an exception that '<literal>ß</literal>' is equal to '<literal>s</literal>', not to '<literal>ss</literal>'. If that is acceptable for your application, use <literal>utf8_general_ci</literal> because it is faster. Otherwise, use <literal>utf8_unicode_ci</literal> because it is ``smarter.'' </para> <para> <literal>utf8_swedish_ci</literal>, like other <literal>utf8</literal> language-specific collations, is derived from <literal>utf8_unicode_ci</literal> with additional language rules. For example, in Swedish, the following relationship holds, which is not something expected by a German or French speaker: </para> <programlisting> Ü = Y < Ö </programlisting> <para> The <literal>utf8_spanish_ci</literal> and <literal>utf8_spanish2_ci</literal> collations correspond to modern Spanish and traditional Spanish, respectively. In both collations, '<literal>ñ</literal>' (n-tilde) is a separate letter between '<literal>n</literal>' and '<literal>o</literal>'. In addition, for traditional Spanish, '<literal>ch</literal>' is a separate letter between '<literal>c</literal>' and <literal>d</literal>, and '<literal>ll</literal>' is a separate letter between '<literal>l</literal>' and '<literal>m</literal>' </para> </section> <section id="charset-we-sets"> <title id='title-charset-we-sets'>&title-charset-we-sets;</title> <para> West European Character Sets cover most West European languages, such as French, Spanish, Catalan, Basque, Portuguese, Italian, Albanian, Dutch, German, Danish, Swedish, Norwegian, Finnish, Faroese, Icelandic, Irish, Scottish, and English. </para> <itemizedlist> <listitem><para> <literal>ascii</literal> (US ASCII) collations: </para> <itemizedlist> <listitem><para> <literal>ascii_bin</literal> </para></listitem> <listitem><para> <literal>ascii_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>cp850</literal> (DOS West European) collations: </para> <itemizedlist> <listitem><para> <literal>cp850_bin</literal> </para></listitem> <listitem><para> <literal>cp850_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>dec8</literal> (DEC West European) collations: </para> <itemizedlist> <listitem><para> <literal>dec8_bin</literal> </para></listitem> <listitem><para> <literal>dec8_swedish_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>hp8</literal> (HP West European) collations: </para> <itemizedlist> <listitem><para> <literal>hp8_bin</literal> </para></listitem> <listitem><para> <literal>hp8_english_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>latin1</literal> (ISO 8859-1 West European) collations: </para> <itemizedlist> <listitem><para> <literal>latin1_bin</literal> </para></listitem> <listitem><para> <literal>latin1_danish_ci</literal> </para></listitem> <listitem><para> <literal>latin1_general_ci</literal> </para></listitem> <listitem><para> <literal>latin1_general_cs</literal> </para></listitem> <listitem><para> <literal>latin1_german1_ci</literal> </para></listitem> <listitem><para> <literal>latin1_german2_ci</literal> </para></listitem> <listitem><para> <literal>latin1_spanish_ci</literal> </para></listitem> <listitem><para> <literal>latin1_swedish_ci</literal> (default) </para></listitem> </itemizedlist> <para> The <literal>latin1</literal> is the default character set. The <literal>latin1_swedish_ci</literal> collation is the default that probably is used by the majority of MySQL customers. It is constantly stated that this is based on the Swedish/Finnish collation rules, but you can find Swedes and Finns who disagree with that statement. </para> <para> The <literal>latin1_german1_ci</literal> and <literal>latin1_german2_ci</literal> collations are based on the DIN-1 and DIN-2 standards, where DIN stands for Deutsches Institut für Normung (that is, the German answer to ANSI). DIN-1 is called the dictionary collation and DIN-2 is called the phone-book collation. </para> <itemizedlist> <listitem><para> <literal>latin1_german1_ci</literal> (dictionary) rules: </para> <programlisting> Ä = A Ö = O Ü = U ß = s </programlisting></listitem> <listitem><para> <literal>latin1_german2_ci</literal> (phone-book) rules: </para> <programlisting> Ä = AE Ö = OE Ü = UE ß = ss </programlisting></listitem> </itemizedlist> <para> In the <literal>latin1_spanish_ci</literal> collation, '<literal>ñ</literal>' (n-tilde) is a separate letter between '<literal>n</literal>' and '<literal>o</literal>'. </para></listitem> <listitem><para> <literal>macroman</literal> (Mac West European) collations: </para> <itemizedlist> <listitem><para> <literal>macroman_bin</literal> </para></listitem> <listitem><para> <literal>macroman_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>swe7</literal> (7bit Swedish) collations: </para> <itemizedlist> <listitem><para> <literal>swe7_bin</literal> </para></listitem> <listitem><para> <literal>swe7_swedish_ci</literal> (default) </para></listitem> </itemizedlist></listitem> </itemizedlist> </section> <section id="charset-ce-sets"> <title id='title-charset-ce-sets'>&title-charset-ce-sets;</title> <para> We have some support for character sets used in the Czech Republic, Slovakia, Hungary, Romania, Slovenia, Croatia, and Poland. </para> <itemizedlist> <listitem><para> <literal>cp1250</literal> (Windows Central European) collations: </para> <itemizedlist> <listitem><para> <literal>cp1250_bin</literal> </para></listitem> <listitem><para> <literal>cp1250_croatian_ci</literal> </para></listitem> <listitem><para> <literal>cp1250_czech_cs</literal> </para></listitem> <listitem><para> <literal>cp1250_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>cp852</literal> (DOS Central European) collations: </para> <itemizedlist> <listitem><para> <literal>cp852_bin</literal> </para></listitem> <listitem><para> <literal>cp852_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>keybcs2</literal> (DOS Kamenicky Czech-Slovak) collations: </para> <itemizedlist> <listitem><para> <literal>keybcs2_bin</literal> </para></listitem> <listitem><para> <literal>keybcs2_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>latin2</literal> (ISO 8859-2 Central European) collations: </para> <itemizedlist> <listitem><para> <literal>latin2_bin</literal> </para></listitem> <listitem><para> <literal>latin2_croatian_ci</literal> </para></listitem> <listitem><para> <literal>latin2_czech_cs</literal> </para></listitem> <listitem><para> <literal>latin2_general_ci</literal> (default) </para></listitem> <listitem><para> <literal>latin2_hungarian_ci</literal> </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>macce</literal> (Mac Central European) collations: </para> <itemizedlist> <listitem><para> <literal>macce_bin</literal> </para></listitem> <listitem><para> <literal>macce_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> </itemizedlist> </section> <section id="charset-se-me-sets"> <title id='title-charset-se-me-sets'>&title-charset-se-me-sets;</title> <itemizedlist> <listitem><para> <literal>armscii8</literal> (ARMSCII-8 Armenian) collations: </para> <itemizedlist> <listitem><para> <literal>armscii8_bin</literal> </para></listitem> <listitem><para> <literal>armscii8_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>cp1256</literal> (Windows Arabic) collations: </para> <itemizedlist> <listitem><para> <literal>cp1256_bin</literal> </para></listitem> <listitem><para> <literal>cp1256_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>geostd8</literal> (GEOSTD8 Georgian) collations: </para> <itemizedlist> <listitem><para> <literal>geostd8_bin</literal> </para></listitem> <listitem><para> <literal>geostd8_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>greek</literal> (ISO 8859-7 Greek) collations: </para> <itemizedlist> <listitem><para> <literal>greek_bin</literal> </para></listitem> <listitem><para> <literal>greek_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>hebrew</literal> (ISO 8859-8 Hebrew) collations: </para> <itemizedlist> <listitem><para> <literal>hebrew_bin</literal> </para></listitem> <listitem><para> <literal>hebrew_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>latin5</literal> (ISO 8859-9 Turkish) collations: </para> <itemizedlist> <listitem><para> <literal>latin5_bin</literal> </para></listitem> <listitem><para> <literal>latin5_turkish_ci</literal> (default) </para></listitem> </itemizedlist></listitem> </itemizedlist> </section> <section id="charset-baltic-sets"> <title id='title-charset-baltic-sets'>&title-charset-baltic-sets;</title> <para> The Baltic character sets cover Estonian, Latvian, and Lithuanian languages. There are two Baltic character sets currently supported: </para> <itemizedlist> <listitem><para> <literal>cp1257</literal> (Windows Baltic) collations: </para> <itemizedlist> <listitem><para> <literal>cp1257_bin</literal> </para></listitem> <listitem><para> <literal>cp1257_general_ci</literal> (default) </para></listitem> <listitem><para> <literal>cp1257_lithuanian_ci</literal> </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>latin7</literal> (ISO 8859-13 Baltic) collations: </para> <itemizedlist> <listitem><para> <literal>latin7_bin</literal> </para></listitem> <listitem><para> <literal>latin7_estonian_cs</literal> </para></listitem> <listitem><para> <literal>latin7_general_ci</literal> (default) </para></listitem> <listitem><para> <literal>latin7_general_cs</literal> </para></listitem> </itemizedlist></listitem> </itemizedlist> </section> <section id="charset-cyrillic-sets"> <title id='title-charset-cyrillic-sets'>&title-charset-cyrillic-sets;</title> <para> Here are the Cyrillic character sets and collations for use with Belarusian, Bulgarian, Russian, and Ukrainian languages. </para> <!-- - It might seem --> <!-- odd that we seem to have a general Cyrillic collation which is --> <!-- supposed to fit all the Cyrillic alphabets except Ukrainian, and one --> <!-- for Ukrainian. The difference only comes up for one character, the --> <!-- soft sign.- --> <itemizedlist> <listitem><para> <literal>cp1251</literal> (Windows Cyrillic) collations: </para> <itemizedlist> <listitem><para> <literal>cp1251_bin</literal> </para></listitem> <listitem><para> <literal>cp1251_bulgarian_ci</literal> </para></listitem> <listitem><para> <literal>cp1251_general_ci</literal> (default) </para></listitem> <listitem><para> <literal>cp1251_general_cs</literal> </para></listitem> <listitem><para> <literal>cp1251_ukrainian_ci</literal> </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>cp866</literal> (DOS Russian) collations: </para> <itemizedlist> <listitem><para> <literal>cp866_bin</literal> </para></listitem> <listitem><para> <literal>cp866_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>koi8r</literal> (KOI8-R Relcom Russian) collations: </para> <itemizedlist> <listitem><para> <literal>koi8r_bin</literal> </para></listitem> <listitem><para> <literal>koi8r_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>koi8u</literal> (KOI8-U Ukrainian) collations: </para> <itemizedlist> <listitem><para> <literal>koi8u_bin</literal> </para></listitem> <listitem><para> <literal>koi8u_general_ci</literal> (default) </para></listitem> </itemizedlist></listitem> </itemizedlist> </section> <section id="charset-asian-sets"> <title id='title-charset-asian-sets'>&title-charset-asian-sets;</title> <para> The Asian character sets that we support include Chinese, Japanese, Korean, and Thai. These can be complicated. For example, the Chinese sets must allow for thousands of different characters. </para> <itemizedlist> <listitem><para> <literal>big5</literal> (Big5 Traditional Chinese) collations: </para> <itemizedlist> <listitem><para> <literal>big5_bin</literal> </para></listitem> <listitem><para> <literal>big5_chinese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>cp932</literal> (SJIS for Windows Japanese) collations: </para> <itemizedlist> <listitem><para> <literal>cp932_bin</literal> </para></listitem> <listitem><para> <literal>cp932_japanese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>eucjpms</literal> (UJIS for Windows Japanese) collations: </para> <itemizedlist> <listitem><para> <literal>eucjpms_bin</literal> </para></listitem> <listitem><para> <literal>eucjpms_japanese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>euckr</literal> (EUC-KR Korean) collations: </para> <itemizedlist> <listitem><para> <literal>euckr_bin</literal> </para></listitem> <listitem><para> <literal>euckr_korean_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>gb2312</literal> (GB2312 Simplified Chinese) collations: </para> <itemizedlist> <listitem><para> <literal>gb2312_bin</literal> </para></listitem> <listitem><para> <literal>gb2312_chinese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>gbk</literal> (GBK Simplified Chinese) collations: </para> <itemizedlist> <listitem><para> <literal>gbk_bin</literal> </para></listitem> <listitem><para> <literal>gbk_chinese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>sjis</literal> (Shift-JIS Japanese) collations: </para> <itemizedlist> <listitem><para> <literal>sjis_bin</literal> </para></listitem> <listitem><para> <literal>sjis_japanese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>tis620</literal> (TIS620 Thai) collations: </para> <itemizedlist> <listitem><para> <literal>tis620_bin</literal> </para></listitem> <listitem><para> <literal>tis620_thai_ci</literal> (default) </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>ujis</literal> (EUC-JP Japanese) collations: </para> <itemizedlist> <listitem><para> <literal>ujis_bin</literal> </para></listitem> <listitem><para> <literal>ujis_japanese_ci</literal> (default) </para></listitem> </itemizedlist></listitem> </itemizedlist> </section> </section> </chapter> --- New file --- +++ refman/client-side-scripts.xml 05/06/15 18:46:57 <?xml version='1.0' encoding='utf-8'?> <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN" "http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" [ <!ENTITY % fixedchars.entities SYSTEM "fixedchars.ent"> %fixedchars.entities; <!ENTITY % title.entities SYSTEM "titles.ent"> %title.entities; ]> <chapter id='client-side-scripts'> <title id='title-client-side-scripts'>&title-client-side-scripts;</title> <!-- TODO: mysqlaccess --> <!-- TODO: point out which programs support the SSL options --> <!-- TODO: option versions where applicable --> <para> There are many different MySQL client programs that connect to the server to access databases or perform administrative tasks. Other utilities are available as well. These do not communicate with the server but perform MySQL-related operations. </para> <para> This chapter provides a brief overview of these programs and then a more detailed description of each one. The descriptions indicate how to invoke the programs and the options they understand. See <xref linkend="using-mysql-programs"/> for general information on invoking programs and specifying program options. </para> <section id="client-side-overview"> <title id='title-client-side-overview'>&title-client-side-overview;</title> <para> The following list briefly describes the MySQL client programs and utilities: </para> <!-- TODO: not listed here: mysqldumpslow --> <itemizedlist> <indexterm type="concept"> <primary><command>myisampack</command></primary> </indexterm> <listitem><para> <literal>myisampack</literal> </para> <para> A utility that compresses <literal>MyISAM</literal> tables to produce smaller read-only tables. See <xref linkend="myisampack"/>. </para> <para> <indexterm type="concept"> <primary><command>mysql</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysql</literal> </para> <para> The command-line tool for interactively entering SQL statements or executing them from a file in batch mode. See <xref linkend="mysql"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlaccess</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlaccess</literal> </para> <para> A script that checks the access privileges for a host, user, and database combination. </para> <para> <indexterm type="concept"> <primary><command>mysqladmin</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqladmin</literal> </para> <para> A client that performs administrative operations, such as creating or dropping databases, reloading the grant tables, flushing tables to disk, and reopening log files. <command>mysqladmin</command> can also be used to retrieve version, process, and status information from the server. See <xref linkend="mysqladmin"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlbinlog</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlbinlog</literal> </para> <para> A utility for reading statements from a binary log. The log of executed statements contained in the binary log files can be used to help recover from a crash. See <xref linkend="mysqlbinlog"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlcc</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlcc</literal> </para> <para> A client that provides a graphical interface for interacting with the server. See <xref linkend="mysqlcc"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlcheck</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlcheck</literal> </para> <para> A table-maintenance client that checks, repairs, analyzes, and optimizes tables. See <xref linkend="using-mysqlcheck"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqldump</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqldump</literal> </para> <para> A client that dumps a MySQL database into a file as SQL statements or as tab-separated text files. Enhanced freeware originally by Igor Romanenko. See <xref linkend="mysqldump"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlhotcopy</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlhotcopy</literal> </para> <para> A utility that quickly makes backups of <literal>MyISAM</literal> or <literal>ISAM</literal> tables while the server is running. See <xref linkend="mysqlhotcopy"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlimport</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlimport</literal> </para> <para> A client that imports text files into their respective tables using <literal>LOAD DATA INFILE</literal>. See <xref linkend="mysqlimport"/>. </para> <para> <indexterm type="concept"> <primary><command>mysqlshow</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>mysqlshow</literal> </para> <para> A client that displays information about databases, tables, columns, and indexes. See <xref linkend="mysqlshow"/>. </para> <para> <indexterm type="concept"> <primary><command>perror</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>perror</literal> </para> <para> A utility that displays the meaning of system or MySQL error codes. See <xref linkend="perror"/>. </para> <para> <indexterm type="concept"> <primary><command>replace</command></primary> </indexterm> </para></listitem> <listitem><para> <literal>replace</literal> </para> <para> A utility program that changes strings in place in files or on the standard input. See <xref linkend="replace-utility"/>. </para></listitem> </itemizedlist> <para> Each MySQL program takes many different options. However, every MySQL program provides a <literal>--help</literal> option that you can use to get a full description of the program's different options. For example, try <command>mysql --help</command>. </para> <indexterm type="concept"> <primary>environment variables</primary> </indexterm> <para> MySQL clients that communicate with the server using the <literal>mysqlclient</literal> library use the following environment variables: </para> <indexterm type="type"> <primary><literal>MYSQL_UNIX_PORT</literal> environment variable</primary> </indexterm> <indexterm type="type"> <primary>environment variable</primary> <secondary><literal>MYSQL_UNIX_PORT</literal></secondary> </indexterm> <indexterm type="type"> <primary><literal>MYSQL_TCP_PORT</literal> environment variable</primary> </indexterm> <indexterm type="type"> <primary>environment variable</primary> <secondary><literal>MYSQL_TCP_PORT</literal></secondary> </indexterm> <indexterm type="type"> <primary><literal>MYSQL_PWD</literal> environment variable</primary> </indexterm> <indexterm type="type"> <primary>environment variable</primary> <secondary><literal>MYSQL_PWD</literal></secondary> </indexterm> <indexterm type="type"> <primary><literal>MYSQL_DEBUG</literal> environment variable</primary> </indexterm> <indexterm type="type"> <primary>environment variable</primary> <secondary><literal>MYSQL_DEBUG</literal></secondary> </indexterm> <informaltable> <tgroup cols="2"> <colspec colwidth="25*"/> <colspec colwidth="75*"/> <tbody> <row> <entry> <literal>MYSQL_UNIX_PORT</literal> </entry> <entry> The default Unix socket file; used for connections to <literal>localhost</literal> </entry> </row> <row> <entry> <literal>MYSQL_TCP_PORT</literal> </entry> <entry> The default port number; used for TCP/IP connections </entry> </row> <row> <entry> <literal>MYSQL_PWD</literal> </entry> <entry> The default password </entry> </row> <row> <entry> <literal>MYSQL_DEBUG</literal> </entry> <entry> Debug trace options when debugging </entry> </row> <row> <entry> <literal>TMPDIR</literal> </entry> <entry> The directory where temporary tables and files are created </entry> </row> </tbody> </tgroup> </informaltable> <para> Use of <literal>MYSQL_PWD</literal> is insecure. See <xref linkend="password-security"/>. </para> <para> You can override the default option values or values specified in environment variables for all standard programs by specifying options in an option file or on the command line. <xref linkend="program-options"/>. </para> </section> <section id="myisampack"> <title id='title-myisampack'>&title-myisampack;</title> <indexterm type="concept"> <primary>compressed tables</primary> </indexterm> <indexterm type="concept"> <primary>tables</primary> <secondary>compressed</secondary> </indexterm> <indexterm type="concept"> <primary>MyISAM</primary> <secondary>compressed tables</secondary> </indexterm> <indexterm type="concept"> <primary><command>myisampack</command></primary> </indexterm> <indexterm type="concept"> <primary><command>pack_isam</command></primary> </indexterm> <para> The <command>myisampack</command> utility compresses <literal>MyISAM</literal> tables. <command>myisampack</command> works by compressing each column in the table separately. Usually, <command>myisampack</command> packs the data file 40%-70%. </para> <para> When the table is used later, the information needed to decompress columns is read into memory. This results in much better performance when accessing individual records, because you only have to uncompress exactly one record, not a much larger disk block as when using Stacker on MS-DOS. </para> <para> MySQL uses <literal>mmap()</literal> when possible to perform memory mapping on compressed tables. If <literal>mmap()</literal> doesn't work, MySQL falls back to normal read/write file operations. </para> <para> A similar utility, <command>pack_isam</command>, compresses <literal>ISAM</literal> tables. Because <literal>ISAM</literal> tables are deprecated, this section discusses only <command>myisampack</command>, but the general procedures for using <command>myisampack</command> are also true for <command>pack_isam</command> unless otherwise specified. </para> <para> Please note the following: </para> <itemizedlist> <listitem><para> If the <command>mysqld</command> server was invoked with the <literal>--skip-external-locking</literal> option, it is not a good idea to invoke <command>myisampack</command> if the table might be updated by the server during the packing process. </para></listitem> <listitem><para> After packing a table, it becomes read-only. This is generally intended (such as when accessing packed tables on a CD). Allowing writes to a packed table is on our TODO list, but with low priority. </para></listitem> <listitem><para> <command>myisampack</command> can pack <literal>BLOB</literal> or <literal>TEXT</literal> columns. The older <command>pack_isam</command> program for <literal>ISAM</literal> tables cannot. </para></listitem> </itemizedlist> <para> Invoke <command>myisampack</command> like this: </para> <programlisting> shell> myisampack [<replaceable>options</replaceable>] <replaceable>filename</replaceable> ... </programlisting> <para> Each filename should be the name of an index (<filename>.MYI</filename>) file. If you are not in the database directory, you should specify the pathname to the file. It is permissible to omit the <filename>.MYI</filename> extension. </para> <para> <command>myisampack</command> supports the following options: </para> <itemizedlist> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para></listitem> <listitem><para> <literal>--backup, -b</literal> </para> <para> Make a backup of the table data file using the name <filename><replaceable>tbl_name</replaceable>.OLD</filename>. </para></listitem> <listitem><para> <literal>--debug[=<replaceable>debug_options</replaceable>], -# [<replaceable>debug_options</replaceable>]</literal> </para> <para> Write a debugging log. The <replaceable>debug_options</replaceable> string often is <literal>'d:t:o,<replaceable>file_name</replaceable>'</literal>. </para></listitem> <listitem><para> <literal>--force, -f</literal> </para> <para> Produce a packed table even if it becomes larger than the original or if the temporary file from an earlier invocation of <command>myisampack</command> exists. (<command>myisampack</command> creates a temporary file named <filename><replaceable>tbl_name</replaceable>.TMD</filename> while it compresses the table. If you kill <command>myisampack</command>, the <filename>.TMD</filename> file might not be deleted.) Normally, <command>myisampack</command> exits with an error if it finds that <filename><replaceable>tbl_name</replaceable>.TMD</filename> exists. With <literal>--force</literal>, <command>myisampack</command> packs the table anyway. </para></listitem> <listitem><para> <literal>--join=<replaceable>big_tbl_name</replaceable>, -j <replaceable>big_tbl_name</replaceable></literal> </para> <para> Join all tables named on the command line into a single table <replaceable>big_tbl_name</replaceable>. All tables that are to be combined <emphasis>must</emphasis> have identical structure (same column names and types, same indexes, and so forth). </para></listitem> <listitem><para> <literal>--packlength=<replaceable>#</replaceable>, -p <replaceable>#</replaceable></literal> </para> <para> Specify the record length storage size, in bytes. The value should be 1, 2, or 3. <command>myisampack</command> stores all rows with length pointers of 1, 2, or 3 bytes. In most normal cases, <command>myisampack</command> can determine the right length value before it begins packing the file, but it may notice during the packing process that it could have used a shorter length. In this case, <command>myisampack</command> prints a note that the next time you pack the same file, you could use a shorter record length. </para></listitem> <listitem><para> <literal>--silent, -s</literal> </para> <para> Silent mode. Write output only when errors occur. </para></listitem> <listitem><para> <literal>--test, -t</literal> </para> <para> Don't actually pack the table, just test packing it. </para></listitem> <listitem><para> <literal>--tmpdir=<replaceable>path</replaceable>, -T <replaceable>path</replaceable></literal> </para> <para> Use the named directory as the location in which to write the temporary table. </para></listitem> <listitem><para> <literal>--verbose, -v</literal> </para> <para> Verbose mode. Write information about the progress of the packing operation and its result. </para></listitem> <listitem><para> <literal>--version, -V</literal> </para> <para> Display version information and exit. </para></listitem> <listitem><para> <literal>--wait, -w</literal> </para> <para> Wait and retry if the table is in use. If the <command>mysqld</command> server was invoked with the <literal>--skip-external-locking</literal> option, it is not a good idea to invoke <command>myisampack</command> if the table might be updated by the server during the packing process. </para></listitem> </itemizedlist> <indexterm type="concept"> <primary>examples</primary> <secondary>compressed tables</secondary> </indexterm> <para> The following sequence of commands illustrates a typical table compression session: </para> <programlisting> shell> ls -l station.* -rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD -rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm shell> myisamchk -dvv station MyISAM file: station Isam-version: 2 Creation time: 1996-03-13 10:08:58 Recover time: 1997-02-02 3:06:43 Data records: 1192 Deleted blocks: 0 Datafile parts: 1192 Deleted data: 0 Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2 Max datafile length: 54657023 Max keyfile length: 33554431 Recordlength: 834 Record format: Fixed length table description: Key Start Len Index Type Root Blocksize Rec/key 1 2 4 unique unsigned long 1024 1024 1 2 32 30 multip. text 10240 1024 1 Field Start Length Type 1 1 1 2 2 4 3 6 4 4 10 1 5 11 20 6 31 1 7 32 30 8 62 35 9 97 35 10 132 35 11 167 4 12 171 16 13 187 35 14 222 4 15 226 16 16 242 20 17 262 20 18 282 20 19 302 30 20 332 4 21 336 4 22 340 1 23 341 8 24 349 8 25 357 8 26 365 2 27 367 2 28 369 4 29 373 4 30 377 1 31 378 2 32 380 8 33 388 4 34 392 4 35 396 4 36 400 4 37 404 1 38 405 4 39 409 4 40 413 4 41 417 4 42 421 4 43 425 4 44 429 20 45 449 30 46 479 1 47 480 1 48 481 79 49 560 79 50 639 79 51 718 79 52 797 8 53 805 1 54 806 1 55 807 20 56 827 4 57 831 4 shell> myisampack station.MYI Compressing station.MYI: (1192 records) - Calculating statistics normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11 pre-space: 0 end-space: 12 table-lookups: 5 zero: 7 Original trees: 57 After join: 17 - Compressing file 87.14% Remember to run myisamchk -rq on compressed tables shell> ls -l station.* -rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD -rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm shell> myisamchk -dvv station MyISAM file: station Isam-version: 2 Creation time: 1996-03-13 10:08:58 Recover time: 1997-04-17 19:04:26 Data records: 1192 Deleted blocks: 0 Datafile parts: 1192 Deleted data: 0 Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1 Max datafile length: 16777215 Max keyfile length: 131071 Recordlength: 834 Record format: Compressed table description: Key Start Len Index Type Root Blocksize Rec/key 1 2 4 unique unsigned long 10240 1024 1 2 32 30 multip. text 54272 1024 1 Field Start Length Type Huff tree Bits 1 1 1 constant 1 0 2 2 4 zerofill(1) 2 9 3 6 4 no zeros, zerofill(1) 2 9 4 10 1 3 9 5 11 20 table-lookup 4 0 6 31 1 3 9 7 32 30 no endspace, not_always 5 9 8 62 35 no endspace, not_always, no empty 6 9 9 97 35 no empty 7 9 10 132 35 no endspace, not_always, no empty 6 9 11 167 4 zerofill(1) 2 9 12 171 16 no endspace, not_always, no empty 5 9 13 187 35 no endspace, not_always, no empty 6 9 14 222 4 zerofill(1) 2 9 15 226 16 no endspace, not_always, no empty 5 9 16 242 20 no endspace, not_always 8 9 17 262 20 no endspace, no empty 8 9 18 282 20 no endspace, no empty 5 9 19 302 30 no endspace, no empty 6 9 20 332 4 always zero 2 9 21 336 4 always zero 2 9 22 340 1 3 9 23 341 8 table-lookup 9 0 24 349 8 table-lookup 10 0 25 357 8 always zero 2 9 26 365 2 2 9 27 367 2 no zeros, zerofill(1) 2 9 28 369 4 no zeros, zerofill(1) 2 9 29 373 4 table-lookup 11 0 30 377 1 3 9 31 378 2 no zeros, zerofill(1) 2 9 32 380 8 no zeros 2 9 33 388 4 always zero 2 9 34 392 4 table-lookup 12 0 35 396 4 no zeros, zerofill(1) 13 9 36 400 4 no zeros, zerofill(1) 2 9 37 404 1 2 9 38 405 4 no zeros 2 9 39 409 4 always zero 2 9 40 413 4 no zeros 2 9 41 417 4 always zero 2 9 42 421 4 no zeros 2 9 43 425 4 always zero 2 9 44 429 20 no empty 3 9 45 449 30 no empty 3 9 46 479 1 14 4 47 480 1 14 4 48 481 79 no endspace, no empty 15 9 49 560 79 no empty 2 9 50 639 79 no empty 2 9 51 718 79 no endspace 16 9 52 797 8 no empty 2 9 53 805 1 17 1 54 806 1 3 9 55 807 20 no empty 3 9 56 827 4 no zeros, zerofill(2) 2 9 57 831 4 no zeros, zerofill(1) 2 9 </programlisting> <para> <command>myisampack</command> displays the following kinds of information: </para> <itemizedlist> <listitem><para> <literal>normal</literal> </para> <para> The number of columns for which no extra packing is used. </para></listitem> <listitem><para> <literal>empty-space</literal> </para> <para> The number of columns containing values that are only spaces; these occupy one bit. </para></listitem> <listitem><para> <literal>empty-zero</literal> </para> <para> The number of columns containing values that are only binary zeros; these occupy one bit. </para></listitem> <listitem><para> <literal>empty-fill</literal> </para> <para> The number of integer columns that don't occupy the full byte range of their type; these are changed to a smaller type. For example, a <literal>BIGINT</literal> column (eight bytes) can be stored as a <literal>TINYINT</literal> column (one byte) if all its values are in the range from <literal>-128</literal> to <literal>127</literal>. </para></listitem> <listitem><para> <literal>pre-space</literal> </para> <para> The number of decimal columns that are stored with leading spaces. In this case, each value contains a count for the number of leading spaces. </para></listitem> <listitem><para> <literal>end-space</literal> </para> <para> The number of columns that have a lot of trailing spaces. In this case, each value contains a count for the number of trailing spaces. </para></listitem> <listitem><para> <literal>table-lookup</literal> </para> <para> The column had only a small number of different values, which were converted to an <literal>ENUM</literal> before Huffman compression. </para></listitem> <listitem><para> <literal>zero</literal> </para> <para> The number of columns for which all values are zero. </para></listitem> <listitem><para> <literal>Original trees</literal> </para> <para> The initial number of Huffman trees. </para></listitem> <listitem><para> <literal>After join</literal> </para> <para> The number of distinct Huffman trees left after joining trees to save some header space. </para></listitem> </itemizedlist> <para> After a table has been compressed, <command>myisamchk -dvv</command> prints additional information about each column: </para> <itemizedlist> <listitem><para> <literal>Type</literal> </para> <para> The column type. The value may contain any of the following descriptors: </para> <itemizedlist> <listitem><para> <literal>constant</literal> </para> <para> All rows have the same value. </para></listitem> <listitem><para> <literal>no endspace</literal> </para> <para> Don't store endspace. </para></listitem> <listitem><para> <literal>no endspace, not_always</literal> </para> <para> Don't store endspace and don't do endspace compression for all values. </para></listitem> <listitem><para> <literal>no endspace, no empty</literal> </para> <para> Don't store endspace. Don't store empty values. </para></listitem> <listitem><para> <literal>table-lookup</literal> </para> <para> The column was converted to an <literal>ENUM</literal>. </para></listitem> <listitem><para> <literal>zerofill(<replaceable>n</replaceable>)</literal> </para> <para> The most significant <replaceable>n</replaceable> bytes in the value are always 0 and are not stored. </para></listitem> <listitem><para> <literal>no zeros</literal> </para> <para> Don't store zeros. </para></listitem> <listitem><para> <literal>always zero</literal> </para> <para> Zero values are stored using one bit. </para></listitem> </itemizedlist></listitem> <listitem><para> <literal>Huff tree</literal> </para> <para> The number of the Huffman tree associated with the column. </para></listitem> <listitem><para> <literal>Bits</literal> </para> <para> The number of bits used in the Huffman tree. </para></listitem> </itemizedlist> <para> After you run <command>myisampack</command>, you must run <command>myisamchk</command> to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently: </para> <programlisting> shell> myisamchk -rq --sort-index --analyze <replaceable>tbl_name</replaceable>.MYI </programlisting> <para> A similar procedure applies for <literal>ISAM</literal> tables. After using <command>pack_isam</command>, use <command>isamchk</command> to re-create the indexes: </para> <programlisting> shell> isamchk -rq --sort-index --analyze <replaceable>tbl_name</replaceable>.ISM </programlisting> <para> After you have installed the packed table into the MySQL database directory, you should execute <command>mysqladmin flush-tables</command> to force <command>mysqld</command> to start using the new table. </para> <para> To unpack a packed table, use the <literal>--unpack</literal> option to <command>myisamchk</command> or <command>isamchk</command>. </para> </section> <section id="mysql"> <title id='title-mysql'>&title-mysql;</title> <!-- TODO: describe input-line editing --> <indexterm type="concept"> <primary>command-line tool</primary> </indexterm> <indexterm type="concept"> <primary>tools</primary> <secondary>command-line</secondary> </indexterm> <indexterm type="concept"> <primary>scripts</primary> <secondary>SQL</secondary> </indexterm> <indexterm type="concept"> <primary>SQL scripts</primary> </indexterm> <indexterm type="concept"> <primary><command>mysql</command></primary> </indexterm> <para> <command>mysql</command> is a simple SQL shell (with GNU <literal>readline</literal> capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command-line options. </para> <para> If you have problems due to insufficient memory for large result sets, use the <literal>--quick</literal> option. This forces <command>mysql</command> to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by using <literal>mysql_use_result()</literal> rather than <literal>mysql_store_result()</literal> to retrieve the result set. </para> <para> Using <command>mysql</command> is very easy. Invoke it from the prompt of your command interpreter as follows: </para> <programlisting> shell> mysql <replaceable>db_name</replaceable> </programlisting> <para> Or: </para> <programlisting> shell> mysql --user=<replaceable>user_name</replaceable> --password=<replaceable>your_password</replaceable> <replaceable>db_name</replaceable> </programlisting> <para> Then type an SQL statement, end it with '<literal>;</literal>', <literal>\g</literal>, or <literal>\G</literal> and press Enter. </para> <para> You can run a script simply like this: </para> <programlisting> shell> mysql <replaceable>db_name</replaceable> < <replaceable>script.sql</replaceable> > <replaceable>output.tab</replaceable> </programlisting> <indexterm type="concept"> <primary><command>mysql</command> command-line options</primary> </indexterm> <indexterm type="concept"> <primary>command-line options</primary> <secondary><command>mysql</command></secondary> </indexterm> <indexterm type="concept"> <primary>options</primary> <secondary>command-line</secondary> <tertiary><command>mysql</command></tertiary> </indexterm> <indexterm type="concept"> <primary>startup parameters</primary> <secondary><command>mysql</command></secondary> </indexterm> <para> <command>mysql</command> supports the following options: </para> <itemizedlist> <indexterm type="concept"> <primary><literal>help</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para> <para> <indexterm type="concept"> <primary><literal>batch</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--batch, -B</literal> </para> <para> Print results using tab as the column separator, with each row on a new line. With this option, <command>mysql</command> doesn't use the history file. </para> <para> <indexterm type="concept"> <primary><literal>character-sets-dir</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--character-sets-dir=<replaceable>path</replaceable></literal> </para> <para> The directory where character sets are installed. See <xref linkend="character-sets"/>. </para> <para> <indexterm type="concept"> <primary><literal>compress</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--compress, -C</literal> </para> <para> Compress all information sent between the client and the server if both support compression. </para> <para> <indexterm type="concept"> <primary><literal>database</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--database=<replaceable>db_name</replaceable>, -D <replaceable>db_name</replaceable></literal> </para> <para> The database to use. This is useful mainly in an option file. </para> <para> <indexterm type="concept"> <primary><literal>debug</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--debug[=<replaceable>debug_options</replaceable>], -# [<replaceable>debug_options</replaceable>]</literal> </para> <para> Write a debugging log. The <replaceable>debug_options</replaceable> string often is <literal>'d:t:o,<replaceable>file_name</replaceable>'</literal>. The default is <literal>'d:t:o,/tmp/mysql.trace'</literal>. </para> <para> <indexterm type="concept"> <primary><literal>debug-info</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--debug-info, -T</literal> </para> <para> Print some debugging information when the program exits. </para> <para> <indexterm type="concept"> <primary><literal>default-character-set</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--default-character-set=<replaceable>charset</replaceable></literal> </para> <para> Use <replaceable>charset</replaceable> as the default character set. See <xref linkend="character-sets"/>. </para> <para> <indexterm type="concept"> <primary><literal>execute</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--execute=<replaceable>statement</replaceable>, -e <replaceable>statement</replaceable></literal> </para> <para> Execute the statement and quit. The default output format is like that produced with <literal>--batch</literal>. </para> <para> <indexterm type="concept"> <primary><literal>force</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--force, -f</literal> </para> <para> Continue even if an SQL error occurs. </para> <para> <indexterm type="concept"> <primary><literal>host</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--host=<replaceable>host_name</replaceable>, -h <replaceable>host_name</replaceable></literal> </para> <para> Connect to the MySQL server on the given host. </para> <para> <indexterm type="concept"> <primary><literal>html</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--html, -H</literal> </para> <para> Produce HTML output. </para> <para> <indexterm type="concept"> <primary><literal>ignore-space</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--ignore-space, -i</literal> </para> <para> Ignore spaces after function names. The effect of this is described in the discussion for <literal>IGNORE_SPACE</literal> in <xref linkend="server-sql-mode"/>. </para> <para> <indexterm type="concept"> <primary><literal>local-infile</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--local-infile[={0|1}]</literal> </para> <para> Enable or disable <literal>LOCAL</literal> capability for <literal>LOAD DATA INFILE</literal>. With no value, the option enables <literal>LOCAL</literal>. It may be given as <literal>--local-infile=0</literal> or <literal>--local-infile=1</literal> to explicitly disable or enable <literal>LOCAL</literal>. Enabling <literal>LOCAL</literal> has no effect if the server does not also support it. </para> <para> <indexterm type="concept"> <primary><literal>named-commands</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--named-commands, -G</literal> </para> <para> Named commands are <emphasis>enabled</emphasis>. Long format commands are allowed as well as shortened \* commands. For example, <literal>quit</literal> and <literal>\q</literal> both are recognized. </para> <para> <indexterm type="concept"> <primary><literal>no-auto-rehash</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--no-auto-rehash, -A</literal> </para> <para> No automatic rehashing. This option causes <command>mysql</command> to start faster, but you must issue the <literal>rehash</literal> command if you want to use table and column name completion. </para> <para> <indexterm type="concept"> <primary><literal>no-beep</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--no-beep, -b</literal> </para> <para> Do not beep when errors occur. </para> <para> <indexterm type="concept"> <primary><literal>no-named-commands</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--no-named-commands, -g</literal> </para> <para> Named commands are disabled. Use the <literal>\*</literal> form only, or use named commands only at the beginning of a line ending with a semicolon ('<literal>;</literal>'). As of MySQL 3.23.22, <command>mysql</command> starts with this option <emphasis>enabled</emphasis> by default! However, even with this option, long-format commands still work from the first line. </para> <para> <indexterm type="concept"> <primary><literal>no-pager</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--no-pager</literal> </para> <para> Do not use a pager for displaying query output. Output paging is discussed further in <xref linkend="mysql-commands"/>. </para> <para> <indexterm type="concept"> <primary><literal>no-tee</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--no-tee</literal> </para> <para> Do not copy output to a file. Tee files are discussed further in <xref linkend="mysql-commands"/>. </para> <para> <indexterm type="concept"> <primary><literal>one-database</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--one-database, -O</literal> </para> <para> Ignore statements except those for the default database named on the command line. This is useful for skipping updates to other databases in the binary log. </para> <para> <indexterm type="concept"> <primary><literal>pager</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--pager[=<replaceable>command</replaceable>]</literal> </para> <para> Use the given command for paging query output. If the command is omitted, the default pager is the value of your <literal>PAGER</literal> environment variable. Valid pagers are <command>less</command>, <command>more</command>, <command>cat [> filename]</command>, and so forth. This option works only on Unix. It does not work in batch mode. Output paging is discussed further in <xref linkend="mysql-commands"/>. </para> <para> <indexterm type="concept"> <primary><literal>password</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--password[=<replaceable>password</replaceable>], -p[<replaceable>password</replaceable>]</literal> </para> <para> The password to use when connecting to the server. If you use the short option form (<literal>-p</literal>), you <emphasis>cannot</emphasis> have a space between the option and the password. If you omit the <replaceable>password</replaceable> value following the <literal>--password</literal> or <literal>-p</literal> option on the command line, you are prompted for one. </para> <para> <indexterm type="concept"> <primary><literal>port</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--port=<replaceable>port_num</replaceable>, -P <replaceable>port_num</replaceable></literal> </para> <para> The TCP/IP port number to use for the connection. </para> <para> <indexterm type="concept"> <primary><literal>prompt</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--prompt=<replaceable>format_str</replaceable></literal> </para> <para> Set the prompt to the specified format. The default is <literal>mysql></literal>. The special sequences that the prompt can contain are described in <xref linkend="mysql-commands"/>. </para> <para> <indexterm type="concept"> <primary><literal>protocol</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--protocol={TCP | SOCKET | PIPE | MEMORY}</literal> </para> <para> The connection protocol to use. New in MySQL 4.1. </para> <para> <indexterm type="concept"> <primary><literal>quick</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--quick, -q</literal> </para> <para> Don't cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, <command>mysql</command> doesn't use the history file. </para> <para> <indexterm type="concept"> <primary><literal>raw</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--raw, -r</literal> </para> <para> Write column values without escape conversion. Often used with the <literal>--batch</literal> option. </para> <para> <indexterm type="concept"> <primary><literal>reconnect</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--reconnect</literal> </para> <para> If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost. To suppress reconnection behavior, use <literal>--skip-reconnect</literal>. New in MySQL 4.1.0. </para> <para> <indexterm type="concept"> <primary><literal>safe-updates</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--safe-updates, --i-am-a-dummy, -U</literal> </para> <para> Allow only <literal>UPDATE</literal> and <literal>DELETE</literal> statements that specify rows to affect using key values. If you have this option in an option file, you can override it by using <literal>--safe-updates</literal> on the command line. See <xref linkend="mysql-tips"/> for more information about this option. </para> <para> <indexterm type="concept"> <primary><literal>secure-auth</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--secure-auth</literal> </para> <para> Do not send passwords to the server in old (pre-4.1.1) format. This prevents connections except for servers that use the newer password format. This option was added in MySQL 4.1.1. </para> <para> <indexterm type="concept"> <primary><literal>show-warnings</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--show-warnings</literal> </para> <para> Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode. This option was added in MySQL 5.0.6. </para> <para> <indexterm type="concept"> <primary><literal>sigint-ignore</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--sigint-ignore</literal> </para> <para> Ignore <literal>SIGINT</literal> signals (typically the result of typing Control-C). This option was added in MySQL 4.1.6. </para> <para> <indexterm type="concept"> <primary><literal>silent</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--silent, -s</literal> </para> <para> Silent mode. Produce less output. This option can be given multiple times to produce less and less output. </para> <para> <indexterm type="concept"> <primary><literal>skip-column-names</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--skip-column-names, -N</literal> </para> <para> Don't write column names in results. </para> <para> <indexterm type="concept"> <primary><literal>skip-line-numbers</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--skip-line-numbers, -L</literal> </para> <para> Don't write line numbers for errors. Useful when you want to compare result files that include error messages. </para> <para> <indexterm type="concept"> <primary><literal>socket</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--socket=<replaceable>path</replaceable>, -S <replaceable>path</replaceable></literal> </para> <para> The socket file to use for the connection. </para> <para> <indexterm type="concept"> <primary><literal>table</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--table, -t</literal> </para> <para> Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode. </para> <para> <indexterm type="concept"> <primary><literal>tee</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--tee=<replaceable>file_name</replaceable></literal> </para> <para> Append a copy of output to the given file. This option does not work in batch mode. Tee files are discussed further in <xref linkend="mysql-commands"/>. </para> <para> <indexterm type="concept"> <primary><literal>unbuffered</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--unbuffered, -n</literal> </para> <para> Flush the buffer after each query. </para> <para> <indexterm type="concept"> <primary><literal>user</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--user=<replaceable>user_name</replaceable>, -u <replaceable>user_name</replaceable></literal> </para> <para> The MySQL username to use when connecting to the server. </para> <para> <indexterm type="concept"> <primary><literal>verbose</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--verbose, -v</literal> </para> <para> Verbose mode. Produce more output. This option can be given multiple times to produce more and more output. (For example, <literal>-v -v -v</literal> produces the table output format even in batch mode.) </para> <para> <indexterm type="concept"> <primary><literal>version</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--version, -V</literal> </para> <para> Display version information and exit. </para> <para> <indexterm type="concept"> <primary><literal>vertical</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--vertical, -E</literal> </para> <para> Print the rows of query output vertically. Without this option, you can specify vertical output for individual statements by terminating them with <literal>\G</literal>. </para> <para> <indexterm type="concept"> <primary><literal>wait</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--wait, -w</literal> </para> <para> If the connection cannot be established, wait and retry instead of aborting. </para> <para> <indexterm type="concept"> <primary><literal>xml</literal></primary> <secondary><command>mysql</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--xml, -X</literal> </para> <para> Produce XML output. </para></listitem> </itemizedlist> <para> You can also set the following variables by using <literal>--<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> options: </para> <indexterm type="concept"> <primary>timeout</primary> <secondary><literal>connect_timeout</literal> variable</secondary> </indexterm> <indexterm type="concept"> <primary><literal>connect_timeout</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>max_allowed_packet</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>max_join_size</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>net_buffer_length</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>select_limit</literal> variable</primary> </indexterm> <itemizedlist> <listitem><para> <literal>connect_timeout</literal> </para> <para> The number of seconds before connection timeout. (Default value is 0.) </para></listitem> <listitem><para> <literal>max_allowed_packet</literal> </para> <para> The maximum packet length to send to or receive from the server. (Default value is 16MB.) </para></listitem> <listitem><para> <literal>max_join_size</literal> </para> <para> The automatic limit for rows in a join when using <literal>--safe-updates</literal>. (Default value is 1,000,000.) </para></listitem> <listitem><para> <literal>net_buffer_length</literal> </para> <para> The buffer size for TCP/IP and socket communication. (Default value is 16KB.) </para></listitem> <listitem><para> <literal>select_limit</literal> </para> <para> The automatic limit for <literal>SELECT</literal> statements when using <literal>--safe-updates</literal>. (Default value is 1,000.) </para></listitem> </itemizedlist> <para> It is also possible to set variables by using <literal>--set-variable=<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> or <literal>-O <replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> syntax. However, this syntax is deprecated as of MySQL 4.0. </para> <indexterm type="type"> <primary><literal>MYSQL_HISTFILE</literal> environment variable</primary> </indexterm> <indexterm type="type"> <primary>environment variable</primary> <secondary><literal>MYSQL_HISTFILE</literal></secondary> </indexterm> <indexterm type="type"> <primary><literal>HOME</literal> environment variable</primary> </indexterm> <indexterm type="type"> <primary>environment variable</primary> <secondary><literal>HOME</literal></secondary> </indexterm> <indexterm type="concept"> <primary>mysql history file</primary> </indexterm> <indexterm type="concept"> <primary>command-line history</primary> <secondary>mysql</secondary> </indexterm> <indexterm type="type"> <primary>.mysql_history file</primary> </indexterm> <para> On Unix, the <command>mysql</command> client writes a record of executed statements to a history file. By default, the history file is named <filename>.mysql_history</filename> and is created in your home directory. To specify a different file, set the value of the <literal>MYSQL_HISTFILE</literal> environment variable. </para> <para> If you do not want to maintain a history file, first remove <filename>.mysql_history</filename> if it exists, and then use either of the following techniques: </para> <itemizedlist> <listitem><para> Set the <literal>MYSQL_HISTFILE</literal> variable to <filename>/dev/null</filename>. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files. </para></listitem> <listitem><para> Create <filename>.mysql_history</filename> as a symbolic link to <filename>/dev/null</filename>: </para> <programlisting> shell> ln -s /dev/null $HOME/.mysql_history </programlisting> <para> You need do this only once. </para></listitem> </itemizedlist> <section id="mysql-commands"> <title id='title-mysql-commands'>&title-mysql-commands;</title> <!-- TODO: describe 4.1 help system --> <para> <command>mysql</command> sends SQL statements that you issue to the server to be executed. There is also a set of commands that <command>mysql</command> itself interprets. For a list of these commands, type <literal>help</literal> or <literal>\h</literal> at the <literal>mysql></literal> prompt: </para> <indexterm type="concept"> <primary>mysql commands</primary> <secondary>list of</secondary> </indexterm> <programlisting> mysql> help MySQL commands: ? (\h) Synonym for `help'. clear (\c) Clear command. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set query delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. </programlisting> <para> Each command has both a long and short form. The long form is not case sensitive; the short form is. The long form can be followed by an optional semicolon terminator, but the short form should not. </para> <para> In the <literal>delimiter</literal> command, you should avoid the use of the backslash ('<literal>\</literal>') character because that is the escape character for MySQL. </para> <para> The <literal>edit</literal>, <literal>nopager</literal>, <literal>pager</literal>, and <literal>system</literal> commands work only in Unix. </para> <indexterm type="concept"> <primary>mysql status command</primary> </indexterm> <para> The <literal>status</literal> command provides some information about the connection and the server you are using. If you are running in <literal>--safe-updates</literal> mode, <literal>status</literal> also prints the values for the <command>mysql</command> variables that affect your queries. </para> <para> To log queries and their output, use the <literal>tee</literal> command. All the data displayed on the screen is appended into a given file. This can be very useful for debugging purposes also. You can enable this feature on the command line with the <literal>--tee</literal> option, or interactively with the <literal>tee</literal> command. The <literal>tee</literal> file can be disabled interactively with the <literal>notee</literal> command. Executing <literal>tee</literal> again re-enables logging. Without a parameter, the previous file is used. Note that <literal>tee</literal> flushes query results to the file after each statement, just before <command>mysql</command> prints its next prompt. </para> <para> Browsing or searching query results in interactive mode by using Unix programs such as <command>less</command>, <command>more</command>, or any other similar program is possible with the <literal>--pager</literal> option. If you specify no value for the option, <command>mysql</command> checks the value of the <literal>PAGER</literal> environment variable and sets the pager to that. Output paging can be enabled interactively with the <literal>pager</literal> command and disabled with <literal>nopager</literal>. The command takes an optional argument; if given, the paging program is set to that. With no argument, the pager is set to the pager that was set on the command line, or <literal>stdout</literal> if no pager was specified. </para> <para> Output paging works only in Unix because it uses the <literal>popen()</literal> function, which doesn't exist on Windows. For Windows, the <literal>tee</literal> option can be used instead to save query output, although this is not as convenient as <literal>pager</literal> for browsing output in some situations. </para> <para> A few tips about the <literal>pager</literal> command: </para> <itemizedlist> <listitem><para> You can use it to write to a file and the results go only to the file: </para> <programlisting> mysql> pager cat > /tmp/log.txt </programlisting> <para> You can also pass any options for the program that you want to use as your pager: </para> <programlisting> mysql> pager less -n -i -S </programlisting></listitem> <listitem><para> In the preceding example, note the <literal>-S</literal> option. You may find it very useful for browsing wide query results. Sometimes a very wide result set is difficult to read on the screen. The <literal>-S</literal> option to <command>less</command> can make the result set much more readable because you can scroll it horizontally using the left-arrow and right-arrow keys. You can also use <literal>-S</literal> interactively within <command>less</command> to switch the horizontal-browse mode on and off. For more information, read the <command>less</command> manual page: </para> <programlisting> shell> man less </programlisting></listitem> <listitem><para> You can specify very complex pager commands for handling query output: </para> <programlisting> mysql> pager cat | tee /dr1/tmp/res.txt \ | tee /dr2/tmp/res2.txt | less -n -i -S </programlisting> <para> In this example, the command would send query results to two files in two different directories on two different filesystems mounted on <filename>/dr1</filename> and <filename>/dr2</filename>, yet still display the results onscreen via <command>less</command>. </para></listitem> </itemizedlist> <para> You can also combine the <literal>tee</literal> and <literal>pager</literal> functions. Have a <command>tee</command> file enabled and <literal>pager</literal> set to <command>less</command>, and you are able to browse the results using the <command>less</command> program and still have everything appended into a file the same time. The difference between the Unix <command>tee</command> used with the <literal>pager</literal> command and the <command>mysql</command> built-in <literal>tee</literal> command is that the built-in <literal>tee</literal> works even if you don't have the Unix <command>tee</command> available. The built-in <literal>tee</literal> also logs everything that is printed on the screen, whereas the Unix <command>tee</command> used with <literal>pager</literal> doesn't log quite that much. Additionally, <literal>tee</literal> file logging can be turned on and off interactively from within <command>mysql</command>. This is useful when you want to log some queries to a file, but not others. </para> <indexterm type="concept"> <primary>mysql prompt command</primary> </indexterm> <para> From MySQL 4.0.2 on, the default <literal>mysql></literal> prompt can be reconfigured. The string for defining the prompt can contain the following special sequences: </para> <!-- FIX these columnfractions have NOT been measured! --> <informaltable> <tgroup cols="2"> <colspec colwidth="15*"/> <colspec colwidth="75*"/> <tbody> <row> <entry> <emphasis role="bold">Option</emphasis> </entry> <entry> <emphasis role="bold">Description</emphasis> </entry> </row> <row> <entry> <literal>\v</literal> </entry> <entry> The server version </entry> </row> <row> <entry> <literal>\d</literal> </entry> <entry> The current database </entry> </row> <row> <entry> <literal>\h</literal> </entry> <entry> The server host </entry> </row> <row> <entry> <literal>\p</literal> </entry> <entry> The current TCP/IP host </entry> </row> <row> <entry> <literal>\u</literal> </entry> <entry> Your username </entry> </row> <row> <entry> <literal>\U</literal> </entry> <entry> Your full <literal><replaceable>user_name</replaceable>@<replaceable>host_name</replaceable></literal> account name </entry> </row> <row> <entry> <literal>\\</literal> </entry> <entry> A literal '<literal>\</literal>' backslash character </entry> </row> <row> <entry> <literal>\n</literal> </entry> <entry> A newline character </entry> </row> <row> <entry> <literal>\t</literal> </entry> <entry> A tab character </entry> </row> <row> <entry> <literal>\ </literal> </entry> <entry> A space (a space follows the backslash) </entry> </row> <row> <entry> <literal>\_</literal> </entry> <entry> A space </entry> </row> <row> <entry> <literal>\R</literal> </entry> <entry> The current time, in 24-hour military time (0-23) </entry> </row> <row> <entry> <literal>\r</literal> </entry> <entry> The current time, standard 12-hour time (1-12) </entry> </row> <row> <entry> <literal>\m</literal> </entry> <entry> Minutes of the current time </entry> </row> <row> <entry> <literal>\y</literal> </entry> <entry> The current year, two digits </entry> </row> <row> <entry> <literal>\Y</literal> </entry> <entry> The current year, four digits </entry> </row> <row> <entry> <literal>\D</literal> </entry> <entry> The full current date </entry> </row> <row> <entry> <literal>\s</literal> </entry> <entry> Seconds of the current time </entry> </row> <row> <entry> <literal>\w</literal> </entry> <entry> The current day of the week in three-letter format (Mon, Tue, ...) </entry> </row> <row> <entry> <literal>\P</literal> </entry> <entry> am/pm </entry> </row> <row> <entry> <literal>\o</literal> </entry> <entry> The current month in numeric format </entry> </row> <row> <entry> <literal>\O</literal> </entry> <entry> The current month in three-letter format (Jan, Feb, ...) </entry> </row> <row> <entry> <literal>\c</literal> </entry> <entry> A counter that increments for each statement you issue </entry> </row> <row> <entry> <literal>\S</literal> </entry> <entry> Semicolon </entry> </row> <row> <entry> <literal>\'</literal> </entry> <entry> Single quote </entry> </row> <row> <entry> <literal>\"</literal> </entry> <entry> Double quote </entry> </row> </tbody> </tgroup> </informaltable> <para> '<literal>\</literal>' followed by any other letter just becomes that letter. </para> <para> If you specify the <literal>prompt</literal> command with no argument, <command>mysql</command> resets the prompt to the default of <literal>mysql></literal>. </para> <para> You can set the prompt in several ways: </para> <itemizedlist> <listitem><para> Use an environment variable </para> <para> You can set the <literal>MYSQL_PS1</literal> environment variable to a prompt string. For example: </para> <programlisting> shell> export MYSQL_PS1="(\u@\h) [\d]> " </programlisting></listitem> <listitem><para> Use an option file </para> <para> You can set the <literal>prompt</literal> option in the <literal>[mysql]</literal> group of any MySQL option file, such as <filename>/etc/my.cnf</filename> or the <filename>.my.cnf</filename> file in your home directory. For example: </para> <programlisting> [mysql] prompt=(\\u@\\h) [\\d]>\\_ </programlisting> <para> In this example, note that the backslashes are doubled. If you set the prompt using the <literal>prompt</literal> option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of allowable prompt options and the set of special escape sequences that are recognized in option files. (These sequences are listed in <xref linkend="option-files"/>.) The overlap may cause you problems if you use single backslashes. For example, <literal>\s</literal> is interpreted as a space rather than as the current seconds value. The following example shows how to define a prompt within an option file to include the current time in <literal>HH:MM:SS></literal> format: </para> <programlisting> [mysql] prompt="\\r:\\m:\\s> " </programlisting></listitem> <listitem><para> Use a command-line option </para> <para> You can set the <literal>--prompt</literal> option on the command line to <command>mysql</command>. For example: </para> <programlisting> shell> mysql --prompt="(\u@\h) [\d]> " (user@host) [database]> </programlisting></listitem> <listitem><para> Interactively </para> <para> You can change your prompt interactively by using the <literal>prompt</literal> (or <literal>\R</literal>) command. For example: </para> <programlisting> mysql> prompt (\u@\h) [\d]>\_ PROMPT set to '(\u@\h) [\d]>\_' (<replaceable>user</replaceable>@<replaceable>host</replaceable>) [<replaceable>database</replaceable>]> (<replaceable>user</replaceable>@<replaceable>host</replaceable>) [<replaceable>database</replaceable>]> prompt Returning to default PROMPT of mysql> mysql> </programlisting></listitem> </itemizedlist> </section> <section id="batch-commands"> <title id='title-batch-commands'>&title-batch-commands;</title> <para> The <command>mysql</command> client typically is used interactively, like this: </para> <programlisting> shell> mysql <replaceable>db_name</replaceable> </programlisting> <para> However, it's also possible to put your SQL statements in a file and then tell <command>mysql</command> to read its input from that file. To do so, create a text file <filename>text_file</filename> that contains the statements you wish to execute. Then invoke <command>mysql</command> as shown here: </para> <programlisting> shell> mysql <replaceable>db_name</replaceable> < <replaceable>text_file</replaceable> </programlisting> <para> You can also start your text file with a <literal>USE <replaceable>db_name</replaceable></literal> statement. In this case, it is unnecessary to specify the database name on the command line: </para> <programlisting> shell> mysql < text_file </programlisting> <para> If you are running <command>mysql</command>, you can execute an SQL script file using the <literal>source</literal> or <literal>\.</literal> command: </para> <programlisting> mysql> source <replaceable>filename</replaceable> mysql> \. <replaceable>filename</replaceable> </programlisting> <para> Sometimes you may want your script to display progress information to the user; for this you can insert some lines like </para> <programlisting> SELECT '<info_to_display>' AS ' '; </programlisting> <para> which outputs <info_to_display>. </para> <para> For more information about batch mode, see <xref linkend="batch-mode"/>. </para> </section> <section id="mysql-tips"> <title id='title-mysql-tips'>&title-mysql-tips;</title> <para> This section describes some techniques that can help you use <command>mysql</command> more effectively. </para> <section id="vertical-query-results"> <title id='title-vertical-query-results'>&title-vertical-query-results;</title> <para> Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output: </para> <programlisting> mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G *************************** 1. row *************************** msg_nro: 3068 date: 2000-03-01 23:29:50 time_zone: +0200 mail_from: Monty reply: monty@stripped mail_to: "Thimble Smith" <tim@stripped> sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes: Thimble> Hi. I think this is a good idea. Is anyone familiar Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my Thimble> TODO list and see what happens. Yes, please do that. Regards, Monty file: inbox-jani-1 hash: 190402944 1 row in set (0.09 sec) </programlisting> </section> <section id="safe-updates"> <title id='title-safe-updates'>&title-safe-updates;</title> <indexterm type="concept"> <primary><literal>safe-updates</literal> option</primary> </indexterm> <para> For beginners, a useful startup option is <literal>--safe-updates</literal> (or <literal>--i-am-a-dummy</literal>, which has the same effect). This option was introduced in MySQL 3.23.11. It is helpful for cases when you might have issued a <literal>DELETE FROM <replaceable>tbl_name</replaceable></literal> statement but forgotten the <literal>WHERE</literal> clause. Normally, such a statement deletes all rows from the table. With <literal>--safe-updates</literal>, you can delete rows only by specifying the key values that identify them. This helps prevent accidents. </para> <para> When you use the <literal>--safe-updates</literal> option, <command>mysql</command> issues the following statement when it connects to the MySQL server: </para> <programlisting> SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000; </programlisting> <para> See <xref linkend="set-option"/>. </para> <para> The <literal>SET</literal> statement has the following effects: </para> <itemizedlist> <listitem><para> You are not allowed to execute an <literal>UPDATE</literal> or <literal>DELETE</literal> statement unless you specify a key constraint in the <literal>WHERE</literal> clause or provide a <literal>LIMIT</literal> clause (or both). For example: </para> <programlisting> UPDATE <replaceable>tbl_name</replaceable> SET <replaceable>not_key_column</replaceable>=# WHERE <replaceable>key_column</replaceable>=#; UPDATE <replaceable>tbl_name</replaceable> SET <replaceable>not_key_column</replaceable>=# LIMIT 1; </programlisting></listitem> <listitem><para> All large <literal>SELECT</literal> results are automatically limited to 1,000 rows unless the statement includes a <literal>LIMIT</literal> clause. </para></listitem> <listitem><para> Multiple-table <literal>SELECT</literal> statements that probably need to examine more than 1,000,000 row combinations are aborted. </para></listitem> </itemizedlist> <para> To specify limits other than 1,000 and 1,000,000, you can override the defaults by using <literal>--select_limit</literal> and <literal>--max_join_size</literal> options: </para> <programlisting> shell> mysql --safe-updates --select_limit=500 --max_join_size=10000 </programlisting> </section> <section id="mysql-reconnect"> <title id='title-mysql-reconnect'>&title-mysql-reconnect;</title> <para> If the <command>mysql</command> client loses its connection to the server while sending a query, it immediately and automatically tries to reconnect once to the server and send the query again. However, even if <command>mysql</command> succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user and session variables. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted without you knowing it: </para> <programlisting> mysql> SET @a=1; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t VALUES(@a); ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 1 row affected (1.30 sec) mysql> SELECT * FROM t; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.05 sec) </programlisting> <para> The <literal>@a</literal> user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have <command>mysql</command> terminate with an error if the connection has been lost, you can start the <command>mysql</command> client with the <literal>--skip-reconnect</literal> option. </para> <!-- TODO: Add mysqlaccess section here --> </section> </section> </section> <section id="mysqladmin"> <title id='title-mysqladmin'>&title-mysqladmin;</title> <indexterm type="concept"> <primary>administration</primary> <secondary>server</secondary> </indexterm> <indexterm type="concept"> <primary>server administration</primary> </indexterm> <indexterm type="concept"> <primary><command>mysqladmin</command></primary> </indexterm> <para> <command>mysqladmin</command> is a client for performing administrative operations. You can use it to check the server's configuration and current status, create and drop databases, and more. </para> <para> Invoke <command>mysqladmin</command> like this: </para> <programlisting> shell> mysqladmin [<replaceable>options</replaceable>] <replaceable>command</replaceable> [<replaceable>command-option</replaceable>] <replaceable>command</replaceable> ... </programlisting> <para> <command>mysqladmin</command> supports the following commands: </para> <itemizedlist> <listitem><para> <literal>create <replaceable>db_name</replaceable></literal> </para> <para> Create a new database named <replaceable>db_name</replaceable>. </para></listitem> <listitem><para> <literal>debug</literal> </para> <para> Tell the server to write debug information to the error log. </para></listitem> <listitem><para> <literal>drop <replaceable>db_name</replaceable></literal> </para> <para> Delete the database named <replaceable>db_name</replaceable> and all its tables. </para></listitem> <listitem><para> <literal>extended-status</literal> </para> <para> Display the server status variables and their values. </para></listitem> <listitem><para> <literal>flush-hosts</literal> </para> <para> Flush all information in the host cache. </para></listitem> <listitem><para> <literal>flush-logs</literal> </para> <para> Flush all logs. </para></listitem> <listitem><para> <literal>flush-privileges</literal> </para> <para> Reload the grant tables (same as <literal>reload</literal>). </para></listitem> <listitem><para> <literal>flush-status</literal> </para> <para> Clear status variables. </para></listitem> <listitem><para> <literal>flush-tables</literal> </para> <para> Flush all tables. </para></listitem> <listitem><para> <literal>flush-threads</literal> </para> <para> Flush the thread cache. (Added in MySQL 3.23.16.) </para></listitem> <listitem><para> <literal>kill id,id,...</literal> </para> <para> Kill server threads. </para></listitem> <listitem><para> <literal>old-password <replaceable>new-password</replaceable></literal> </para> <para> This is like the <literal>password</literal> command but stores the password using the old (pre-4.1) password-hashing format. This command was added in MySQL 4.1.0. </para></listitem> <listitem><para> <literal>password <replaceable>new-password</replaceable></literal> </para> <para> Set a new password. This changes the password to <literal>new-password</literal> for the account that you use with <command>mysqladmin</command> for connecting to the server. </para> <para> If <replaceable>new-password</replaceable> contains spaces or other characters that are special to your command interpreter, you need to enclose it within quotes. On Windows, be sure to use double quotes rather than single quotes; single quotes are not be stripped from the password, they are interpreted as part of the password. For example: </para> <para> shell> mysqladmin password "my new password" </para></listitem> <listitem><para> <literal>ping</literal> </para> <para> Check whether the server is alive. The return status from <command>mysqladmin</command> is 0 if the server is running, 1 if it is not. Beginning with MySQL 4.0.22, the status is 0 even in case of an error such as <literal>Access denied</literal>, because that means the server is running but disallowed the connection, which is different from the server not running. </para></listitem> <listitem><para> <literal>processlist</literal> </para> <para> Show a list of active server threads. This is like the output of the <literal>SHOW PROCESSLIST</literal> statement. If the <literal>--verbose</literal> option is given, the output is like that of <literal>SHOW FULL PROCESSLIST</literal>. </para></listitem> <listitem><para> <literal>reload</literal> </para> <para> Reload the grant tables. </para></listitem> <listitem><para> <literal>refresh</literal> </para> <para> Flush all tables and close and open log files. </para></listitem> <listitem><para> <literal>shutdown</literal> </para> <para> Stop the server. </para></listitem> <listitem><para> <literal>start-slave</literal> </para> <para> Start replication on a slave server. (Added in MySQL 3.23.16.) </para></listitem> <listitem><para> <literal>status</literal> </para> <para> Display a short server status message. </para></listitem> <listitem><para> <literal>stop-slave</literal> </para> <para> Stop replication on a slave server. (Added in MySQL 3.23.16.) </para></listitem> <listitem><para> <literal>variables</literal> </para> <para> Display the server system variables and their values. </para></listitem> <listitem><para> <literal>version</literal> </para> <para> Display version information from the server. </para></listitem> </itemizedlist> <para> All commands can be shortened to any unique prefix. For example: </para> <programlisting> shell> mysqladmin proc stat +----+-------+-----------+----+-------------+------+-------+------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+----+-------------+------+-------+------+ | 6 | monty | localhost | | Processlist | 0 | | | +----+-------+-----------+----+-------------+------+-------+------+ Uptime: 10077 Threads: 1 Questions: 9 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2 Memory in use: 1092K Max memory used: 1116K </programlisting> <indexterm type="concept"> <primary>status command</primary> <secondary>results</secondary> </indexterm> <para> The <command>mysqladmin status</command> command result displays the following values: </para> <itemizedlist> <indexterm type="concept"> <primary>uptime</primary> </indexterm> <listitem><para> <literal>Uptime</literal> </para> <para> The number of seconds the MySQL server has been running. </para> <para> <indexterm type="concept"> <primary>threads</primary> </indexterm> </para></listitem> <listitem><para> <literal>Threads</literal> </para> <para> The number of active threads (clients). </para> <para> <indexterm type="concept"> <primary>questions</primary> </indexterm> </para></listitem> <listitem><para> <literal>Questions</literal> </para> <para> The number of questions (queries) from clients since the server was started. </para> <para> <indexterm type="concept"> <primary>slow queries</primary> </indexterm> </para></listitem> <listitem><para> <literal>Slow queries</literal> </para> <para> The number of queries that have taken more than <literal>long_query_time</literal> seconds. See <xref linkend="slow-query-log"/>. </para> <para> <indexterm type="concept"> <primary>opens</primary> </indexterm> </para></listitem> <listitem><para> <literal>Opens</literal> </para> <para> The number of tables the server has opened. </para> <para> <indexterm type="concept"> <primary>flush tables</primary> </indexterm> <indexterm type="concept"> <primary>tables</primary> <secondary>flush</secondary> </indexterm> </para></listitem> <listitem><para> <literal>Flush tables</literal> </para> <para> The number of <literal>flush ...</literal>, <literal>refresh</literal>, and <literal>reload</literal> commands the server has executed. </para> <para> <indexterm type="concept"> <primary>open tables</primary> </indexterm> </para></listitem> <listitem><para> <literal>Open tables</literal> </para> <para> The number of tables that currently are open. </para> <para> <indexterm type="concept"> <primary>memory use</primary> </indexterm> </para></listitem> <listitem><para> <literal>Memory in use</literal> </para> <para> The amount of memory allocated directly by <command>mysqld</command> code. This value is displayed only when MySQL has been compiled with <literal>--with-debug=full</literal>. </para> <para> <indexterm type="concept"> <primary>maximum memory used</primary> </indexterm> </para></listitem> <listitem><para> <literal>Maximum memory used</literal> </para> <para> The maximum amount of memory allocated directly by <command>mysqld</command> code. This value is displayed only when MySQL has been compiled with <literal>--with-debug=full</literal>. </para></listitem> </itemizedlist> <para> If you execute <command>mysqladmin shutdown</command> when connecting to a local server using a Unix socket file, <command>mysqladmin</command> waits until the server's process ID file has been removed, to ensure that the server has stopped properly. </para> <indexterm type="concept"> <primary><command>mysqladmin</command> command-line options</primary> </indexterm> <indexterm type="concept"> <primary>command-line options</primary> <secondary><command>mysqladmin</command></secondary> </indexterm> <indexterm type="concept"> <primary>options</primary> <secondary>command-line</secondary> <tertiary><command>mysqladmin</command></tertiary> </indexterm> <indexterm type="concept"> <primary>startup parameters</primary> <secondary><command>mysqladmin</command></secondary> </indexterm> <para> <command>mysqladmin</command> supports the following options: </para> <itemizedlist> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para></listitem> <listitem><para> <literal>--character-sets-dir=<replaceable>path</replaceable></literal> </para> <para> The directory where character sets are installed. See <xref linkend="character-sets"/>. </para></listitem> <listitem><para> <literal>--compress, -C</literal> </para> <para> Compress all information sent between the client and the server if both support compression. </para></listitem> <listitem><para> <literal>--count=<replaceable>#</replaceable>, -c <replaceable>#</replaceable></literal> </para> <para> The number of iterations to make. This works only with <literal>--sleep</literal> (<literal>-i</literal>). </para></listitem> <listitem><para> <literal>--debug[=<replaceable>debug_options</replaceable>], -# [<replaceable>debug_options</replaceable>]</literal> </para> <para> Write a debugging log. The <replaceable>debug_options</replaceable> string often is <literal>'d:t:o,<replaceable>file_name</replaceable>'</literal>. The default is <literal>'d:t:o,/tmp/mysqladmin.trace'</literal>. </para></listitem> <listitem><para> <literal>--default-character-set=<replaceable>charset</replaceable></literal> </para> <para> Use <replaceable>charset</replaceable> as the default character set. See <xref linkend="character-sets"/>. Added in MySQL 4.1.9. </para></listitem> <listitem><para> <literal>--force, -f</literal> </para> <para> Don't ask for confirmation for the <literal>drop database</literal> command. With multiple commands, continue even if an error occurs. </para></listitem> <listitem><para> <literal>--host=<replaceable>host_name</replaceable>, -h <replaceable>host_name</replaceable></literal> </para> <para> Connect to the MySQL server on the given host. </para></listitem> <listitem><para> <literal>--password[=<replaceable>password</replaceable>], -p[<replaceable>password</replaceable>]</literal> </para> <para> The password to use when connecting to the server. If you use the short option form (<literal>-p</literal>), you <emphasis>cannot</emphasis> have a space between the option and the password. If you omit the <replaceable>password</replaceable> value following the <literal>--password</literal> or <literal>-p</literal> option on the command line, you are prompted for one. </para></listitem> <listitem><para> <literal>--port=<replaceable>port_num</replaceable>, -P <replaceable>port_num</replaceable></literal> </para> <para> The TCP/IP port number to use for the connection. </para></listitem> <listitem><para> <literal>--protocol={TCP | SOCKET | PIPE | MEMORY}</literal> </para> <para> The connection protocol to use. New in MySQL 4.1. </para></listitem> <listitem><para> <literal>--relative, -r</literal> </para> <para> Show the difference between the current and previous values when used with <literal>-i</literal>. Currently, this option works only with the <literal>extended-status</literal> command. </para></listitem> <listitem><para> <literal>--silent, -s</literal> </para> <para> Exit silently if a connection to the server cannot be established. </para></listitem> <listitem><para> <literal>--sleep=<replaceable>delay</replaceable>, -i <replaceable>delay</replaceable></literal> </para> <para> Execute commands again and again, sleeping for <replaceable>delay</replaceable> seconds in between. </para></listitem> <listitem><para> <literal>--socket=<replaceable>path</replaceable>, -S <replaceable>path</replaceable></literal> </para> <para> The socket file to use for the connection. </para></listitem> <listitem><para> <literal>--user=<replaceable>user_name</replaceable>, -u <replaceable>user_name</replaceable></literal> </para> <para> The MySQL username to use when connecting to the server. </para></listitem> <listitem><para> <literal>--verbose, -v</literal> </para> <para> Verbose mode. Print out more information on what the program does. </para></listitem> <listitem><para> <literal>--version, -V</literal> </para> <para> Display version information and exit. </para></listitem> <listitem><para> <literal>--vertical, -E</literal> </para> <para> Print output vertically. This is similar to <literal>--relative</literal>, but prints output vertically. </para></listitem> <listitem><para> <literal>--wait[=<replaceable>#</replaceable>], -w[<replaceable>#</replaceable>]</literal> </para> <para> If the connection cannot be established, wait and retry instead of aborting. If an option value is given, it indicates the number of times to retry. The default is one time. </para></listitem> </itemizedlist> <para> You can also set the following variables by using <literal>--<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> options: </para> <indexterm type="concept"> <primary>timeout</primary> <secondary><literal>connect_timeout</literal> variable</secondary> </indexterm> <indexterm type="concept"> <primary><literal>connect_timeout</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary>timeout</primary> <secondary><literal>shutdown_timeout</literal> variable</secondary> </indexterm> <indexterm type="concept"> <primary><literal>shutdown_timeout</literal> variable</primary> </indexterm> <itemizedlist> <listitem><para> <literal>connect_timeout</literal> </para> <para> The maximum number of seconds before connection timeout. The default value is 43200 (12 hours). </para></listitem> <listitem><para> <literal>shutdown_timeout</literal> </para> <para> The maximum number of seconds to wait for shutdown. The default value is 3600 (1 hour). </para></listitem> </itemizedlist> <para> It is also possible to set variables by using <literal>--set-variable=<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> or <literal>-O <replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> syntax. However, this syntax is deprecated as of MySQL 4.0. </para> </section> <section id="mysqlbinlog"> <title id='title-mysqlbinlog'>&title-mysqlbinlog;</title> <indexterm type="concept"> <primary><command>mysqlbinlog</command></primary> </indexterm> <para> The binary log files that the server generates are written in binary format. To examine these files in text format, use the <command>mysqlbinlog</command> utility. It is available as of MySQL 3.23.14. </para> <para> Invoke <command>mysqlbinlog</command> like this: </para> <programlisting> shell> mysqlbinlog [<replaceable>options</replaceable>] <replaceable>log-file</replaceable> ... </programlisting> <para> For example, to display the contents of the binary log <filename>binlog.000003</filename>, use this command: </para> <programlisting> shell> mysqlbinlog binlog.0000003 </programlisting> <para> The output includes all statements contained in <filename>binlog.000003</filename>, together with other information such as the time each statement took, the thread ID of the client that issued it, the timestamp when it was issued, and so forth. </para> <para> Normally, you use <command>mysqlbinlog</command> to read binary log files directly and apply them to the local MySQL server. It is also possible to read binary logs from a remote server by using the <literal>--read-from-remote-server</literal> option. </para> <para> When you read remote binary logs, the connection parameter options can be given to indicate how to connect to the server, but they are ignored unless you also specify the <literal>--read-from-remote-server</literal> option. These options are <literal>--host</literal>, <literal>--password</literal>, <literal>--port</literal>, <literal>--protocol</literal>, <literal>--socket</literal>, and <literal>--user</literal>. </para> <para> You can also use <command>mysqlbinlog</command> to read relay log files written by a slave server in a replication setup. Relay logs have the same format as binary log files. </para> <para> The binary log is discussed further in <xref linkend="binary-log"/>. </para> <para> <command>mysqlbinlog</command> supports the following options: </para> <itemizedlist> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para></listitem> <listitem><para> <literal>--database=<replaceable>db_name</replaceable>, -d <replaceable>db_name</replaceable></literal> </para> <para> List entries for just this database (local log only). </para></listitem> <listitem><para> <literal>--force-read, -f</literal> </para> <para> With this option, if <command>mysqlbinlog</command> reads a binary log event that it does not recognize, it prints a warning, ignores the event, and continues. Without this option, <command>mysqlbinlog</command> stops if it reads such an event. </para></listitem> <listitem><para> <literal>--host=<replaceable>host_name</replaceable>, -h <replaceable>host_name</replaceable></literal> </para> <para> Get the binary log from the MySQL server on the given host. </para></listitem> <listitem><para> <literal>--local-load=<replaceable>path</replaceable>, -l <replaceable>path</replaceable></literal> </para> <para> Prepare local temporary files for <literal>LOAD DATA INFILE</literal> in the specified directory. </para></listitem> <listitem><para> <literal>--offset=<replaceable>N</replaceable>, -o <replaceable>N</replaceable></literal> </para> <para> Skip the first <replaceable>N</replaceable> entries. </para></listitem> <listitem><para> <literal>--password[=<replaceable>password</replaceable>], -p[<replaceable>password</replaceable>]</literal> </para> <para> The password to use when connecting to the server. If you use the short option form (<literal>-p</literal>), you <emphasis>cannot</emphasis> have a space between the option and the password. If you omit the <replaceable>password</replaceable> value following the <literal>--password</literal> or <literal>-p</literal> option on the command line, you are prompted for one. </para></listitem> <listitem><para> <literal>--port=<replaceable>port_num</replaceable>, -P <replaceable>port_num</replaceable></literal> </para> <para> The TCP/IP port number to use for connecting to a remote server. </para></listitem> <listitem><para> <literal>--position=<replaceable>N</replaceable>, -j <replaceable>N</replaceable></literal> </para> <para> Deprecated, use <literal>--start-position</literal> instead (starting from MySQL 4.1.4). </para></listitem> <listitem><para> <literal>--protocol={TCP | SOCKET | PIPE | MEMORY}</literal> </para> <para> The connection protocol to use. New in MySQL 4.1. </para></listitem> <listitem><para> <literal>--read-from-remote-server, -R</literal> </para> <para> Read the binary log from a MySQL server. Any connection parameter options are ignored unless this option is given as well. These options are <literal>--host</literal>, <literal>--password</literal>, <literal>--port</literal>, <literal>--protocol</literal>, <literal>--socket</literal>, and <literal>--user</literal>. </para></listitem> <listitem><para> <literal>--result-file=<replaceable>name</replaceable>, -r <replaceable>name</replaceable></literal> </para> <para> Direct output to the given file. </para></listitem> <listitem><para> <literal>--short-form, -s</literal> </para> <para> Display only the statements contained in the log, without any extra information. </para></listitem> <listitem><para> <literal>--socket=<replaceable>path</replaceable>, -S <replaceable>path</replaceable></literal> </para> <para> The socket file to use for the connection. </para></listitem> <listitem><para> <literal>--start-datetime=<replaceable>datetime</replaceable></literal> </para> <para> Start reading the binary log at the first event having a datetime equal to or later than the <literal>datetime</literal> argument. The <replaceable>datetime</replaceable> value is relative to the local time zone on the machine where you run <command>mysqlbinlog</command>. The value should be in a format accepted for the <literal>DATETIME</literal> or <literal>TIMESTAMP</literal> data types. For example: </para> <para> <programlisting> shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003 </programlisting> </para> <para> This option is available as of MySQL 4.1.4. It is useful for point-in-time recovery. </para></listitem> <listitem><para> <literal>--stop-datetime=<replaceable>datetime</replaceable></literal> </para> <para> Stop reading the binary log at the first event having a datetime equal or posterior to the <literal>datetime</literal> argument. See the description of the <literal>--start-datetime</literal> option for information about the <literal>datetime value</literal>. This option is available as of MySQL 4.1.4. It is useful for point-in-time recovery. </para></listitem> <listitem><para> <literal>--start-position=<replaceable>N</replaceable></literal> </para> <para> Start reading the binary log at the first event having a position equal to the <literal>N</literal> argument. Available as of MySQL 4.1.4 (previously named <literal>--position</literal>). </para></listitem> <listitem><para> <literal>--stop-position=<replaceable>N</replaceable></literal> </para> <para> Stop reading the binary log at the first event having a position equal or greater than the <literal>N</literal> argument. Available as of MySQL 4.1.4. </para></listitem> <listitem><para> <literal>--to-last-log, -t</literal> </para> <para> Do not stop at the end of the requested binary log of the MySQL server, but rather continue printing until the end of the last binary log. If you send the output to the same MySQL server, this may lead to an endless loop. This option requires <literal>--read-from-remote-server</literal>. Available as of MySQL 4.1.2. </para></listitem> <listitem><para> <literal>--disable-log-bin, -D</literal> </para> <para> Disable binary logging. This is useful for avoiding an endless loop if you use the <literal>--to-last-log</literal> option and are sending the output to the same MySQL server. This option also is useful when restoring after a crash to avoid duplication of the statements you have logged. <emphasis role="bold">Note</emphasis>: This option requires that you have the <literal>SUPER</literal> privilege. Available as of MySQL 4.1.8. </para></listitem> <listitem><para> <literal>--user=<replaceable>user_name</replaceable>, -u <replaceable>user_name</replaceable></literal> </para> <para> The MySQL username to use when connecting to a remote server. </para></listitem> <listitem><para> <literal>--version, -V</literal> </para> <para> Display version information and exit. </para></listitem> </itemizedlist> <para> You can also set the following variable by using <literal>--<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> options: </para> <indexterm type="concept"> <primary><literal>open_files_limit</literal> variable</primary> </indexterm> <itemizedlist> <listitem><para> <literal>open_files_limit</literal> </para> <para> Specify the number of open file descriptors to reserve. </para></listitem> </itemizedlist> <para> You can pipe the output of <command>mysqlbinlog</command> into a <command>mysql</command> client to execute the statements contained in the binary log. This is used to recover from a crash when you have an old backup (see <xref linkend="backup"/>): </para> <programlisting> shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.000001 | mysql </programlisting> <para> Or: </para> <programlisting> shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.[0-9]* | mysql </programlisting> <para> You can also redirect the output of <command>mysqlbinlog</command> to a text file instead, if you need to modify the statement log first (for example, to remove statements that you don't want to execute for some reason). After editing the file, execute the statements that it contains by using it as input to the <command>mysql</command> program. </para> <para> <command>mysqlbinlog</command> has the <literal>--position</literal> option, which prints only those statements with an offset in the binary log greater than or equal to a given position (the given position must match the start of one event). It also has options to stop or start when it sees an event of a given date and time. This enables you to perform point-in-time recovery using the <literal>--stop-datetime</literal> option (to be able to say, for example, "roll forward my databases to how they were today at 10:30 AM"). </para> <para> If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be <emphasis>unsafe</emphasis>: </para> <programlisting> shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.000001 | mysql # DANGER!! shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.000002 | mysql # DANGER!! </programlisting> <para> Processing binary logs this way using different connections to the server causes problems if the first log file contains a <literal>CREATE TEMPORARY TABLE</literal> statement and the second log contains a statement that uses the temporary table. When the first <command>mysql</command> process terminates, the server drops the temporary table. When the second <command>mysql</command> process attempts to use the table, the server reports ``unknown table.'' </para> <para> To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do that: </para> <programlisting> shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.000001 <replaceable>hostname</replaceable>-bin.000002 | mysql </programlisting> <para> Another approach is to do this: </para> <programlisting> shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.000001 > /tmp/statements.sql shell> mysqlbinlog <replaceable>hostname</replaceable>-bin.000002 >> /tmp/statements.sql shell> mysql -e "source /tmp/statements.sql" </programlisting> <para> In MySQL 3.23, the binary log did not contain the data to load for <literal>LOAD DATA INFILE</literal> statements. To execute such a statement from a binary log file, the original data file was needed. Starting from MySQL 4.0.14, the binary log does contain the data, so <command>mysqlbinlog</command> can produce output that reproduces the <literal>LOAD DATA INFILE</literal> operation without the original data file. <command>mysqlbinlog</command> copies the data to a temporary file and writes a <literal>LOAD DATA LOCAL INFILE</literal> statement that refers to the file. The default location of the directory where these files are written is system-specific. To specify a directory explicitly, use the <literal>--local-load</literal> option. </para> <para> Because <command>mysqlbinlog</command> converts <literal>LOAD DATA INFILE</literal> statements to <literal>LOAD DATA LOCAL INFILE</literal> statements (that is, it adds <literal>LOCAL</literal>), both the client and the server that you use to process the statements must be configured to allow <literal>LOCAL</literal> capability. See <xref linkend="load-data-local"/>. </para> <para> <emphasis role="bold">Warning:</emphasis> The temporary files created for <literal>LOAD DATA LOCAL</literal> statements are <emphasis>not</emphasis> automatically deleted because they are needed until you actually execute those statements. You should delete the temporary files yourself after you no longer need the statement log. The files can be found in the temporary file directory and have names like <filename>original_file_name-#-#</filename>. </para> <para> In the future, we will fix this problem by allowing <command>mysqlbinlog</command> to connect directly to a <command>mysqld</command> server. Then it is possible to safely remove the log files automatically as soon as the <literal>LOAD DATA INFILE</literal> statements have been executed. </para> <para> Before MySQL 4.1, <command>mysqlbinlog</command> could not prepare output suitable for <command>mysql</command> if the binary log contained interlaced statements originating from different clients that used temporary tables of the same name. This is fixed in MySQL 4.1. However, the problem still existed for <literal>LOAD DATA INFILE</literal> statements until it was fixed in MySQL 4.1.8. </para> </section> <section id="mysqlcc"> <title id='title-mysqlcc'>&title-mysqlcc;</title> <indexterm type="concept"> <primary>graphical tool</primary> </indexterm> <indexterm type="concept"> <primary>GUI tool</primary> </indexterm> <indexterm type="concept"> <primary>tools</primary> <secondary>graphical</secondary> </indexterm> <indexterm type="concept"> <primary>tools</primary> <secondary>GUI</secondary> </indexterm> <indexterm type="concept"> <primary><command>mysqlcc</command></primary> </indexterm> <para> <command>mysqlcc</command>, the MySQL Control Center, is a platform-independent client that provides a graphical user interface (GUI) to the MySQL database server. It supports interactive use, including syntax highlighting and tab completion. It provides database and table management, and allows server administration. </para> <para> <command>mysqlcc</command> is deprecated and it is recommended that users choose the new MySQL Administrator and MySQL Query Browser, found at <ulink url="http://dev.mysql.com/downloads/">http://dev.mysql.com/downloads/</ulink>. </para> <!-- @command{mysqlcc} is not included with MySQL distributions, but can be downloaded --> <!-- separately at @uref{http://dev.mysql.com/downloads/}. --> <para> Currently, <command>mysqlcc</command> runs on Windows and Linux platforms. </para> <para> Invoke <command>mysqlcc</command> by double-clicking its icon in a graphical environment. From the command line, invoke it like this: </para> <programlisting> shell> mysqlcc [<replaceable>options</replaceable>] </programlisting> <indexterm type="concept"> <primary><command>mysqlcc</command> command-line options</primary> </indexterm> <indexterm type="concept"> <primary>command-line options</primary> <secondary><command>mysqlcc</command></secondary> </indexterm> <indexterm type="concept"> <primary>options</primary> <secondary>command-line</secondary> <tertiary><command>mysqlcc</command></tertiary> </indexterm> <indexterm type="concept"> <primary>startup parameters</primary> <secondary><command>mysqlcc</command></secondary> </indexterm> <para> <command>mysqlcc</command> supports the following options: </para> <itemizedlist> <indexterm type="concept"> <primary><literal>help</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para> <para> <indexterm type="concept"> <primary><literal>blocking_queries</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--blocking_queries, -b</literal> </para> <para> Use blocking queries. </para> <para> <indexterm type="concept"> <primary><literal>compress</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--compress, -C</literal> </para> <para> Compress all information sent between the client and the server if both support compression. </para> <para> <indexterm type="concept"> <primary><literal>connection_name</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--connection_name=<replaceable>name</replaceable>, -c <replaceable>name</replaceable></literal> </para> <para> This option is a synonym for <literal>--server</literal>. </para> <para> <indexterm type="concept"> <primary><literal>database</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--database=<replaceable>db_name</replaceable>, -d <replaceable>db_name</replaceable></literal> </para> <para> The database to use. This is useful mainly in an option file. </para> <para> <indexterm type="concept"> <primary><literal>history_size</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--history_size=<replaceable>#</replaceable>, -H <replaceable>#</replaceable></literal> </para> <para> The history size for the query window. </para> <para> <indexterm type="concept"> <primary><literal>host</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--host=<replaceable>host_name</replaceable>, -h <replaceable>host_name</replaceable></literal> </para> <para> Connect to the MySQL server on the given host. </para> <para> <indexterm type="concept"> <primary><literal>local-infile</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--local-infile[={0|1}]</literal> </para> <para> Enable or disable <literal>LOCAL</literal> capability for <literal>LOAD DATA INFILE</literal>. With no value, the option enables <literal>LOCAL</literal>. It may be given as <literal>--local-infile=0</literal> or <literal>--local-infile=1</literal> to explicitly disable or enable <literal>LOCAL</literal>. Enabling <literal>LOCAL</literal> has no effect if the server does not also support it. </para> <para> <indexterm type="concept"> <primary><literal>password</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--password[=<replaceable>password</replaceable>], -p[<replaceable>password</replaceable>]</literal> </para> <para> The password to use when connecting to the server. If you use the short option form (<literal>-p</literal>), you <emphasis>cannot</emphasis> have a space between the option and the password. If you omit the <replaceable>password</replaceable> value following the <literal>--password</literal> or <literal>-p</literal> option on the command line, you are prompted for one. </para> <para> <indexterm type="concept"> <primary><literal>plugins_path</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--plugins_path=<replaceable>name</replaceable>, -g <replaceable>name</replaceable></literal> </para> <para> The path to the directory where MySQL Control Center plugins are located. </para> <para> <indexterm type="concept"> <primary><literal>port</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--port=<replaceable>port_num</replaceable>, -P <replaceable>port_num</replaceable></literal> </para> <para> The TCP/IP port number to use for the connection. </para> <para> <indexterm type="concept"> <primary><literal>query</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--query, -q</literal> </para> <para> Open a query window on startup. </para> <para> <indexterm type="concept"> <primary><literal>register</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--register, -r</literal> </para> <para> Open the Register Server dialog on startup. </para> <para> <indexterm type="concept"> <primary><literal>server</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--server=<replaceable>name</replaceable>, -s <replaceable>name</replaceable></literal> </para> <para> The MySQL Control Center connection name. </para> <para> <indexterm type="concept"> <primary><literal>socket</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--socket=<replaceable>path</replaceable>, -S <replaceable>path</replaceable></literal> </para> <para> The socket file to use for the connection. </para> <para> <indexterm type="concept"> <primary><literal>syntax</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--syntax, -y</literal> </para> <para> Enable syntax highlighting and completion. </para> <para> <indexterm type="concept"> <primary><literal>syntax_file</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--syntax_file=<replaceable>name</replaceable>, -Y <replaceable>name</replaceable></literal> </para> <para> The syntax file for completion. </para> <para> <indexterm type="concept"> <primary><literal>translations_path</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--translations_path=<replaceable>name</replaceable>, -T <replaceable>name</replaceable></literal> </para> <para> The path to the directory where MySQL Control Center translations are located. </para> <para> <indexterm type="concept"> <primary><literal>user</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--user=<replaceable>user_name</replaceable>, -u <replaceable>user_name</replaceable></literal> </para> <para> The MySQL username to use when connecting to the server. </para> <para> <indexterm type="concept"> <primary><literal>version</literal></primary> <secondary><command>mysqlcc</command> option</secondary> </indexterm> </para></listitem> <listitem><para> <literal>--version, -V</literal> </para> <para> Display version information and exit. </para></listitem> </itemizedlist> <para> You can also set the following variables by using <literal>--<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> options: </para> <indexterm type="concept"> <primary>timeout</primary> <secondary><literal>connect_timeout</literal> variable</secondary> </indexterm> <indexterm type="concept"> <primary><literal>connect_timeout</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>max_allowed_packet</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>max_join_size</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>net_buffer_length</literal> variable</primary> </indexterm> <indexterm type="concept"> <primary><literal>select_limit</literal> variable</primary> </indexterm> <itemizedlist> <listitem><para> <literal>connect_timeout</literal> </para> <para> The number of seconds before connection timeout. (Default value is 0.) </para></listitem> <listitem><para> <literal>max_allowed_packet</literal> </para> <para> The maximum packet length to send to or receive from the server. (Default value is 16MB.) </para></listitem> <listitem><para> <literal>max_join_size</literal> </para> <para> The automatic limit for rows in a join. (Default value is 1,000,000.) </para></listitem> <listitem><para> <literal>net_buffer_length</literal> </para> <para> The buffer size for TCP/IP and socket communication. (Default value is 16KB.) </para></listitem> <listitem><para> <literal>select_limit</literal> </para> <para> The automatic limit for <literal>SELECT</literal> statements. (Default value is 1,000.) </para></listitem> </itemizedlist> <para> It is also possible to set variables by using <literal>--set-variable=<replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> or <literal>-O <replaceable>var_name</replaceable>=<replaceable>value</replaceable></literal> syntax. However, this syntax is deprecated as of MySQL 4.0. </para> </section> <section id="using-mysqlcheck"> <title id='title-using-mysqlcheck'>&title-using-mysqlcheck;</title> <!-- TODO: This is no longer fully accurate. mysqlcheck can be used for some --> <!-- operations on table types other than MyISAM. --> <!-- TODO: There probably should be more reference to this program from within --> <!-- the table maintenance section. --> <para> The <command>mysqlcheck</command> client checks and repairs <literal>MyISAM</literal> tables. It can also optimize and analyze tables. <command>mysqlcheck</command> is available as of MySQL 3.23.38. </para> <para> <command>mysqlcheck</command> is similar in function to <command>myisamchk</command>, but works differently. The main operational difference is that <command>mysqlcheck</command> must be used when the <command>mysqld</command> server is running, whereas <command>myisamchk</command> should be used when it is not. The benefit of using <command>mysqlcheck</command> is that you do not have to stop the server to check or repair your tables. </para> <para> <command>mysqlcheck</command> uses the SQL statements <literal>CHECK TABLE</literal>, <literal>REPAIR TABLE</literal>, <literal>ANALYZE TABLE</literal>, and <literal>OPTIMIZE TABLE</literal> in a convenient way for the user. It determines which statements to use for the operation you want to perform, then sends the statements to the server to be executed. </para> <para> There are three general ways to invoke <command>mysqlcheck</command>: </para> <programlisting> shell> mysqlcheck [<replaceable>options</replaceable>] <replaceable>db_name</replaceable> [<replaceable>tables</replaceable>] shell> mysqlcheck [<replaceable>options</replaceable>] --databases <replaceable>DB1</replaceable> [<replaceable>DB2</replaceable> <replaceable>DB3</replaceable>...] shell> mysqlcheck [<replaceable>options</replaceable>] --all-databases </programlisting> <para> If you don't name any tables or use the <literal>--databases</literal> or <literal>--all-databases</literal> option, entire databases are checked. </para> <para> <command>mysqlcheck</command> has a special feature compared to the other clients. The default behavior of checking tables (<literal>--check</literal>) can be changed by renaming the binary. If you want to have a tool that repairs tables by default, you should just make a copy of <command>mysqlcheck</command> named <command>mysqlrepair</command>, or make a symbolic link to <command>mysqlcheck</command> named <command>mysqlrepair</command>. If you invoke <literal>mysqlrepair</literal>, it repairs tables on command. </para> <para> The following names can be used to change <command>mysqlcheck</command> default behavior: </para> <informaltable> <tgroup cols="2"> <colspec colwidth="25*"/> <colspec colwidth="75*"/> <tbody> <row> <entry> <command>mysqlrepair</command> </entry> <entry> The default option is <literal>--repair</literal> </entry> </row> <row> <entry> <command>mysqlanalyze</command> </entry> <entry> The default option is <literal>--analyze</literal> </entry> </row> <row> <entry> <command>mysqloptimize</command> </entry> <entry> The default option is <literal>--optimize</literal> </entry> </row> </tbody> </tgroup> </informaltable> <para> <command>mysqlcheck</command> supports the following options: </para> <itemizedlist> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para></listitem> <listitem><para> <literal>--all-databases, -A</literal> </para> <para> Check all tables in all databases. This is the same as using the <literal>--databases</literal> option and naming all the databases on the command line. </para></listitem> <listitem><para> <literal>--all-in-1, -1</literal> </para> <para> Instead of issuing a statement for each table, execute a single statement for each database that names all the tables from that database to be processed. </para></listitem> <listitem><para> <literal>--analyze, -a</literal> </para> <para> Analyze the tables. </para></listitem> <listitem><para> <literal>--auto-repair</literal> </para> <para> If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked. </para></listitem> <listitem><para> <literal>--character-sets-dir=<replaceable>path</replaceable></literal> </para> <para> The directory where character sets are installed. See <xref linkend="character-sets"/>. </para></listitem> <listitem><para> <literal>--check, -c</literal> </para> <para> Check the tables for errors. </para></listitem> <listitem><para> <literal>--check-only-changed, -C</literal> </para> <para> Check only tables that have changed since the last check or that haven't been closed properly. </para></listitem> <listitem><para> <literal>--compress</literal> </para> <para> Compress all information sent between the client and the server if both support compression. </para></listitem> <listitem><para> <literal>--databases, -B</literal> </para> <para> Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names. </para></listitem> <listitem><para> <literal>--debug[=<replaceable>debug_options</replaceable>], -# [<replaceable>debug_options</replaceable>]</literal> </para> <para> Write a debugging log. The <replaceable>debug_options</replaceable> string often is <literal>'d:t:o,<replaceable>file_name</replaceable>'</literal>. </para></listitem> <listitem><para> <literal>--default-character-set=<replaceable>charset</replaceable></literal> </para> <para> Use <replaceable>charset</replaceable> as the default character set. See <xref linkend="character-sets"/>. </para></listitem> <listitem><para> <literal>--extended, -e</literal> </para> <para> If you are using this option to check tables, it ensures that they are 100% consistent but takes a long time. </para> <para> If you are using this option to repair tables, it runs an extended repair that may not only take a long time to execute, but may produce a lot of garbage rows also! </para></listitem> <listitem><para> <literal>--fast, -F</literal> </para> <para> Check only tables that haven't been closed properly. </para></listitem> <listitem><para> <literal>--force, -f</literal> </para> <para> Continue even if an SQL error occurs. </para></listitem> <listitem><para> <literal>--host=<replaceable>host_name</replaceable>, -h <replaceable>host_name</replaceable></literal> </para> <para> Connect to the MySQL server on the given host. </para></listitem> <listitem><para> <literal>--medium-check, -m</literal> </para> <para> Do a check that is faster than an <literal>--extended</literal> operation. This finds only 99.99% of all errors, which should be good enough in most cases. </para></listitem> <listitem><para> <literal>--optimize, -o</literal> </para> <para> Optimize the tables. </para></listitem> <listitem><para> <literal>--password[=<replaceable>password</replaceable>], -p[<replaceable>password</replaceable>]</literal> </para> <para> The password to use when connecting to the server. If you use the short option form (<literal>-p</literal>), you <emphasis>cannot</emphasis> have a space between the option and the password. If you omit the <replaceable>password</replaceable> value following the <literal>--password</literal> or <literal>-p</literal> option on the command line, you are prompted for one. </para></listitem> <listitem><para> <literal>--port=<replaceable>port_num</replaceable>, -P <replaceable>port_num</replaceable></literal> </para> <para> The TCP/IP port number to use for the connection. </para></listitem> <listitem><para> <literal>--protocol={TCP | SOCKET | PIPE | MEMORY}</literal> </para> <para> The connection protocol to use. New in MySQL 4.1. </para></listitem> <listitem><para> <literal>--quick, -q</literal> </para> <para> If you are using this option to check tables, it prevents the check from scanning the rows to check for incorrect links. This is the fastest check method. </para> <para> If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method. </para></listitem> <listitem><para> <literal>--repair, -r</literal> </para> <para> Do a repair that can fix almost anything except unique keys that aren't unique. </para></listitem> <listitem><para> <literal>--silent, -s</literal> </para> <para> Silent mode. Print only error messages. </para></listitem> <listitem><para> <literal>--socket=<replaceable>path</replaceable>, -S <replaceable>path</replaceable></literal> </para> <para> The socket file to use for the connection. </para></listitem> <listitem><para> <literal>--tables</literal> </para> <para> Overrides the <literal>--databases</literal> or <literal>-B</literal> option. All arguments following the option are regarded as table names. </para></listitem> <listitem><para> <literal>--user=<replaceable>user_name</replaceable>, -u <replaceable>user_name</replaceable></literal> </para> <para> The MySQL username to use when connecting to the server. </para></listitem> <listitem><para> <literal>--verbose, -v</literal> </para> <para> Verbose mode. Print information about the various stages of program operation. </para></listitem> <listitem><para> <literal>--version, -V</literal> </para> <para> Display version information and exit. </para></listitem> </itemizedlist> </section> <section id="mysqldump"> <title id='title-mysqldump'>&title-mysqldump;</title> <!-- TODO: indicate what privileges are required for the various dump operations --> <!-- SELECT, LOCK, RELOAD --> <indexterm type="concept"> <primary>dumping</primary> <secondary>databases</secondary> </indexterm> <indexterm type="concept"> <primary>databases</primary> <secondary>dumping</secondary> </indexterm> <indexterm type="concept"> <primary>tables</primary> <secondary>dumping</secondary> </indexterm> <indexterm type="concept"> <primary>backing up</primary> <secondary>databases</secondary> </indexterm> <indexterm type="concept"> <primary><command>mysqldump</command></primary> </indexterm> <para> The <command>mysqldump</command> client can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table and/or populate the table. </para> <para> If you are doing a backup on the server, and your tables all are <literal>MyISAM</literal> tables, you could consider using the <command>mysqlhotcopy</command> instead (faster backup, faster restore). See <xref linkend="mysqlhotcopy"/>. </para> <para> There are three general ways to invoke <command>mysqldump</command>: </para> <programlisting> shell> mysqldump [<replaceable>options</replaceable>] <replaceable>db_name</replaceable> [<replaceable>tables</replaceable>] shell> mysqldump [<replaceable>options</replaceable>] --databases <replaceable>DB1</replaceable> [<replaceable>DB2</replaceable> <replaceable>DB3</replaceable>...] shell> mysqldump [<replaceable>options</replaceable>] --all-databases </programlisting> <para> If you don't name any tables or use the <literal>--databases</literal> or <literal>--all-databases</literal> option, entire databases are dumped. </para> <para> To get a list of the options your version of <command>mysqldump</command> supports, execute <command>mysqldump --help</command>. </para> <para> If you run <command>mysqldump</command> without the <literal>--quick</literal> or <literal>--opt</literal> option, <command>mysqldump</command> loads the whole result set into memory before dumping the result. This probably is a problem if you are dumping a big database. As of MySQL 4.1, <literal>--opt</literal> is on by default, but can be disabled with <literal>--skip-opt</literal>. </para> <para> If you are using a recent copy of the <command>mysqldump</command> program to generate a dump to be reloaded into a very old MySQL server, you should not use the <literal>--opt</literal> or <literal>-e</literal> options. </para> <para> Before MySQL 4.1.2, out-of-range numeric values such as <literal>-inf</literal> and <literal>inf</literal>, as well as NaN (not-a-number) values are dumped by <command>mysqldump</command> as <literal>NULL</literal>. You can see this using the following sample table: </para> <programlisting> mysql> CREATE TABLE t (f DOUBLE); mysql> INSERT INTO t VALUES(1e+111111111111111111111); mysql> INSERT INTO t VALUES(-1e111111111111111111111); mysql> SELECT f FROM t; +------+ | f | +------+ | inf | | -inf | +------+ </programlisting> <para> For this table, <command>mysqldump</command> produces the following data output: </para> <programlisting> -- -- Dumping data for table `t` -- INSERT INTO t VALUES (NULL); INSERT INTO t VALUES (NULL); </programlisting> <para> The significance of this behavior is that if you dump and restore the table, the new table has contents that differ from the original contents. This problem is fixed as of MySQL 4.1.2; you cannot insert <literal>inf</literal> in the table, so this <command>mysqldump</command> behavior is only relevant when you deal with old servers. </para> <para> <command>mysqldump</command> supports the following options: </para> <itemizedlist> <listitem><para> <literal>--help, -?</literal> </para> <para> Display a help message and exit. </para></listitem> <listitem><para> <literal>--add-drop-table</literal> </para> <para> Add a <literal>DROP TABLE</literal> statement before each <literal>CREATE TABLE</literal> statement. </para></listitem> <listitem><para> <literal>--add-locks</literal> </para> <para> Surround each table dump with <literal>LOCK TABLES</literal> and <literal>UNLOCK TABLES</literal> statements. This results in faster inserts when the dump file is reloaded. See <xref linkend="insert-speed"/>. </para></listitem> <listitem><para> <literal>--all-databases, -A</literal> </para> <para> Dump all tables in all databases. This is the same as using the <literal>--databases</literal> option and naming all the databases on the command line. </para></listitem> <listitem><para> <literal>--allow-keywords</literal> </para> <para> Allow creation of column names that are keywords. This works by prefixing each column name with the table name. </para></listitem> <listitem><para> <literal>--comments[={0|1}]</literal> </para> <para> If set to <literal>0</literal>, suppresses additional information in the dump file such as program version, server version, and host. <literal>--skip-comments</literal> has the same effect as <literal>--comments=0</literal>. The default value is <literal>1</literal> to not suppress the extra information. New in MySQL 4.0.17. </para></listitem> <listitem><para> <literal>--compact</literal> </para> <para> Produce less verbose output. This option suppresses comments and enables the <literal>--skip-add-drop-table</literal>, <literal>--no-set-names</literal>, <literal>--skip-disable-keys</literal>, and <literal>--skip-add-locks</literal> options. New in MySQL 4.1.2. </para></listitem> <listitem><para> <literal>--compatible=<replaceable>name</replaceable></literal> </para> <para> Produce output that is compatible with other database systems or with older MySQL servers. The value of <literal>name</literal> can be <literal>ansi</literal>, <literal>mysql323</literal>, <literal>mysql40</literal>, <literal>postgresql</literal>, <literal>oracle</literal>, <literal>mssql</literal>, <literal>db2</literal>, <literal>maxdb</literal>, <literal>no_key_options</literal>, <literal>no_table_options</literal>, or <literal>no_field_options</literal>. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See <xref linkend="server-sql-mode"/>. </para> <para> This option requires a server version of 4.1.0 or higher. With older servers, it does nothing. </para></listitem> <listitem><para> <literal>--complete-insert, -c</literal> </para> <para> Use complete <literal>INSERT</literal> statements that include column names. </para></listitem> <listitem><para> <literal>--compress, -C</literal> </para> <para> Compress all information sent between the client and the server if both support compression. </para></listitem> <listitem><para> <literal>--create-options</literal> </para> <para> Include all MySQL-specific table options in the <literal>CREATE TABLE</literal> statements. Before MySQL 4.1.2, use <literal>--all</literal> instead. </para></listitem> <listitem><para> <literal>--databases, -B</literal> </para> <para> Dump several databases. Normally, <command>mysqldump</command> treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. <literal>CREATE DATABASE IF NOT EXISTS <replaceable>db_name</replaceable></literal> and <literal>USE <replaceable>db_name</replaceable></literal> statements are included in the output before each new database. </para></listitem> <listitem><para> <literal>--debug[=<replaceable>debug_options</replaceable>], -# [<replaceable>debug_options</replaceable>]</literal> </para> <para> Write a debugging log. The <replaceable>debug_options</replaceable> string often is <literal>'d:t:o,<replaceable>file_name</replaceable>'</literal>. </para></listitem> <listitem><para> <literal>--default-character-set=<replaceable>charset</replaceable></literal> </para> <para> Use <replaceable>charset</replaceable> as the default character set. See <xref linkend="character-sets"/>. If not specified, <command>mysqldump</command> from MySQL 4.1.2 or later uses <literal>utf8</literal>, and earlier versions use <literal>latin1</literal>. </para></listitem> <listitem><para> <literal>--delayed-insert</literal> </para> <para> Insert rows using <literal>INSERT DELAYED</literal> statements. This option was disabled in MySQL 5.0.7. </para></listitem> <listitem><para> <literal>--delete-master-logs</literal> </para> <para> On a master replication server, delete the binary logs after performing the dump operation. This option automatically enables <literal>--first-slave</literal> before MySQL 4.1.8 and enables <literal>--master-data</literal> thereafter. It was added in MySQL 3.23.57 (for MySQL 3.23) and MySQL 4.0.13 (for MySQL 4.0). </para></listitem> <listitem><para> <literal>--disable-keys, -K</literal> </para> <para> For each table, surround the <literal>INSERT</literal> statements with <literal>/*!40000 ALTER TABLE <replaceable>tbl_name</replaceable> DISABLE KEYS */;</literal> and <literal>/*!40000 ALTER TABLE <replaceable>tbl_name</replaceable> ENABLE KEYS */;</literal> statements. This makes loading the dump file into a MySQL 4.0 server faster because the indexes are created after all rows are inserted. This option is effective only for <literal>MyISAM</literal> tables. </para></listitem> <listitem><para> <literal>--extended-insert, -e</literal> </para> <para> Use multiple-row <literal>INSERT</literal> syntax that include several <literal>VALUES</literal> lists. This results in a smaller dump file and speeds up inserts when the file is reloaded. </para></listitem> <listitem><para> <literal>--fields-terminated-by=...</literal> , <literal>--fields-enclosed-by=...</literal> , <literal>--fields-optionally-enclosed-by=...</literal> , <literal>--fields-escaped-by=...</literal> , <literal>--lines-terminated-by=...</literal> </para> <para>
| Thread | ||
|---|---|---|
| • bk commit - mysqldoc@docsrva tree (paul:1.2796) | paul | 16 Jun |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 2.403 sec. using MySQL 5.1.14-beta-log