Mysql Chinese Reference Manual 2 (General Information of MySQL) Reprinted
Translation: 子
〖
Back to〗 Forward〗
Translator: 子 (clyan@sohu.com) Home: http://linuxdb.yeah.net1 mysql General Information This is a MySQL reference manual; it records mysql version 3.23.7-alpha. MySQL is a fast, multi-thread, multi-user, and strong SQL database server. For UNIX and OS / 2 platforms, MySQL is basically free; but the Microsoft platform, you must get a MySQL license after 30 days of trial period. See Section 3 MySQL License and Technical Support. The MySQL homepage provides the latest information about MySQL. For the discussion of MySQL capabilities, see the main features of 1.4 mysql. For installation guides, see 4 Install MySQL. For a description of G. Transplantation to other systems for the migration of mysql to new machines or operating systems. For information on upgrading from version 3.21, see 4.16.2 Upgrade from a 3.21 version to 3.22. Getting Started with MySQL, see 8 mysql tutorial. Examples of SQL and benchmark information see the reference directory ('sql-bench' directory in distribution). For new features and errors to repair a history, see D MySQL changes. For a list of current known errors and feature defects, see E MySQL known errors and design defects. Future plans, see f We want to join the Mysql schedule (TODO) in the future. The list of all contributors of this program see C MySQL contributors. Important: Reporting a bug (wrong), ascends, and recommendations to the mailing list (the original text is not available). Distribute to source code, mysqlbug scripts can be found in the 'scripts' directory. For binary distribution, MySQLBUG can be found in the 'bin' directory. If you have any suggestions about the addition or correction of this manual, please send them to the manual group (DOCS@mysql.com). 1.1 What is mysql? MySQL is a real multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular and standardized database language in the world. MySQL is an implementation of a client / server structure that consists of a server daemon MySQLD and a lot of different client programs and libraries. SQL is a standardized language that makes it easier to store, update, and access information. For example, you can use the SQL language to retrieve product information and store customer information as a website, while Mysql is also fast enough to allow you to store record files and images. The main goal of mysql is fast, robust and easy to use. Originally because we need such a SQL server, it can handle the large databases of the manufacturer providing the database on a quantitude in an unuuble hardware platform, but the speed is faster, and MySQL is developed. Since 1996, we have been using mysql, and its environment has more than 40 databases, including 10,000 tables, of which more than 7 million rows, which is about 100 guitar articles (GB). data. The basis of MySQL is based on a set of utility routines that have been used in high-demand production environments. Although MySQL is still in development, it has provided a rich and extremely useful function set. The official pronunciation of MySQL is "My Ess Que Ell" (not my-sequel). 1.2 About this manual Currently this manual has a version of TexInfo, ordinary text, info, html, postscript, and PDF. Because of their length, PostScript and PDF versions are not included in the main MySQL distribution, but can be downloaded from http://www.mysql.com. The main document is a TexInfo file, and the HTML version automatically uses a TEXI2HTML to improve version generation. Ordinary text and INFO are generated with makeinfo.
The PostScript version is generated by Texi2DVI and DVIPS. The PDF version is generated with PDFTex. This manual is maintained by David Axmark, Michael (Monty) Widenius, Paul Dubois and Kim Aldale. Other contributors, see C MySQL contributors. 1.2.1 The convention used in this manual This manual uses a certain text format: constant / fixed width fixed width font is used to command name and selection, SQL statement, database, table, name, C, and Perl code, environment variable. For example: "To understand how mysqladmin work, call it with --help options." 'File name' has a fixed width font of the bundled quotation for file names and paths. For example: "The release version is installed in '/ usr / local /' directory". The 'C' has a fixed width font of the encircled rating is also used to specify a character sequence. For example: "To specify a wildcard, use '%' characters. Intragonal The font of the italic is used to emphasize LIKE THIS. Bold bold is used to access the right name (for example, "Do not authorize Process permissions") and express specifically. When the command is presented by a specific program execution, the program is pointed out by the prompt displayed by the command. For example, shell> indicates that you perform a command from your login housing, and mysql> indicates that you execute the command from MySQL Customer: Shell> Type a shell command in this, type a mysql command shell command with a Bourne Shell syntax display. If you are using the CSH style shell, you may need to send a command slightly different way. For example, set a environment variable and a sequence running a command looks like this: shell> varName = value Some_command For csh, you will perform such sequence: shell> setENV VARNAME VALUESHELL> Some_command database, table and column name It is often necessary to enter the command. To indicate that this amount is necessary, this manual uses DB_NAME, TBL_NAME, and COL_NAME. For example, you may see the statement like this: mysql> select col_name from db_name.tbl_name; this means you want to enter a similar statement, you will provide your own database, table and column name, maybe like this: mysql> SELECT Author_name from biblio_db.author_list; SQL statement can be written in uppercase or lowercase. When this manual displays the SQL statement, if these keywords are discussed, the uppercase is used for a specific keyword (emphasize them) and lowercase other parts of the statement. So you may see the following display when discussing the SELECT statement: mysql> select count (*) from TBL_NAME; on the other hand, the statement will be written as this: mysql> select count (*) from TBL_NAME; If you don't intentionally emphasize, all keywords are written in uppercase. In the syntax description, square brackets ('[' and ']') are used to represent optional words or clauses: DROP TABLE [IF EXISTS] TBL_NAME When a syntax element consists of many selection, each selected vertical line Separate ('|'). When you may select a member from a set of options, select it in square brackets.
When a member must be selected from a set of selections ('{' and '}'): Trim ([[Both | Leading | Trailing] [RESTR] from] str) {describe | DESC} TBL_NAME {Col_name | Wild} 1.3 MySQL History We have started to use MSQL to connect our database tables with our own Fast Bottom (ISAM) utility, however, we have gotten it after some tests: MSQL is for us Demand is not fast and flexible. This leads to a new SQL interface to connect our database, but it has almost the same application programming interface as MSQL. Select this application programming interface to facilitate third party code transplant. The origin of the mysql name is not very clear. Our base catalog and a lot of libraries and tools have a prefix "My" has been more than 10 years, however, Monty's daughter (young age) is also named "My". So which one of them gives MySQL this name is still a mystery, even for us. 1.4 Mysql's main feature The following table describes some important features of MySQL: complete multi-threads using core threads. This means that it can easily use multiple CPUs (if any). C, C , Eiffel, Java, Perl, PHP, Python, and TCL API. See 20 mysql client tools and APIs. You can run on a different platform, see the operating system supported by the 4.2 mysql. Many column types: 1, 2, 3, 4, and 8 bytes of symbolic / unsigned integer, float, double, char, varchar, text, blob, date, time, datetime, timestamp, year, set, ENUM type. See the 7.3 column type. Using an optimized scanning multi-Sweep Multi-join, Join. In the SELECT and WHERE part of the query support all operators and functions, for example: mysql> select concat (first_name, ", las_name) from tbl_name where income / dependents> 10000 and age> 30; implement SQL through a highly optimized class library Function libraries and as fast as they can achieve, usually there should be no memory allocation after query initialization. Fully support SQL's Group By and Order By clauses, support polymerization functions (count (), count (distance), avg (), STD (), SUM (), max (), and min ()). Supports ANSI SQL LEFT OTER JOIN and ODBC syntax. You can mix the table from different databases in the same query. (The same as version 3.22). A very flexible and secure permission and password system, and it allows host-based authentication. The password is secure because all password transfer is encrypted when connecting to a server. Odbc for windiws 95. All ODBC 2. 5 functions and many other functions. For example, you can connect your MySQL server with Access. See 16 mysql ODBC support. Fast B tree magnetic disc table with index compression. Each table allows 16 indexes. Each index can consist of 1 to 16 columns or columns. The maximum index length is 256 bytes (it can change when compiling mysql). A index can use a prefix of a char or varchar field. Decorative and become long records. Used as a list of internal memory of the temporary table. Big database processing. We are using MySQL for some databases that contain 50,000,000 records. All columns have default values, you can insert a subset of a table column with INSERT, and those columns that do not have a clear give value are set to their default.
GNU Automake, AutoConf and Libtool are used for portability. Written with C and C and test with a large number of compilers. A very fast thread-based memory allocation system. There is no memory vulnerability. Tested (Purify) with a commercial memory vulnerability monitoring program. Including a fast utility of Myisamchk, an inspection, optimization, and fixing database tables, see 13 Maintenance MySQL installation. Fully support ISO-8859-1 Latin1 character sets. For example, Scandinavia characters @ringccent {a}, @ "a and @" o is allowed in the table and column name. All data is saved in ISO-8859-1 Latin1 format. All normal strings are ignored. Sort by the ISO-8859-1 Latin1 character set (current Swedish way). It can be changed by increasing the sort order array in the source code. To understand a more advanced sorting example, take a look at the Czech sort code. MySQL supports a lot of different character sets that can be specified at compile. The alias of the tables and columns meets the SQL92 standard. DELETE, INSERT, REPLACE and UPDATE Returns how many rows are changed (affected). The function name will not conflict with the table or column name. For example, ABS is a valid column name. The only limit for function call is a function name and subsequent "(" Cannot have spaces. See 7.31 MySQL is very picky for the reserved word? All mysql programs can be used to help. The server can be Customers provide erroneous messages in multiple languages, see which languages supported 9.1 mysql?. Client uses TCP / IP connection or Unix socket (socket) or NT's named pipe connection mysql. MySQL unique show command can be used to retrieve The information of the database, table, and index can be used to determine how the optimizer solves a query. 1.5 mysql stability? This section answers such a question: "MySQL stability?" And "I can rely on MySQL in this project ? ". Here we will try to clarify some questions and answer more important questions that seem to be concerned about people. This section has been integrated with the information collected from the mailing list (which is very active in the report). TCX MySQL runs without any problems in our scheduled mid-1996. When Mysql is published more openly, we noticed that there are some "untested code" pieces very quickly from our query way new users. Discover. Each new version has fewer portability issues than the previous one (although there are many new features for each issue), and we hope to mark one of the next version as "stable". Each mysql The issuance is available, and only when the user uses the code from "gray zone", of course, the unknowing users can't know what the gray zone is; this section tries to reveal what we are currently known. Here. Description involves mysql 3. 22.x version. All known and reported errors are corrected in the latest versions, except for errors related to "Design N" in the Error Section. See e mysql known Errors and design defects. Mysql is written in multi-layer structures and different independent modules, which are listed below to indicate how each of them is well tested: ISAM table handlers - Stabilize it to manage all in MySQL 3.22 and the data stored in the earlier versions. In all MySQL versions, there is no separate (report) error in the code. The only known method for getting a corrupted database table is to kill the server in one update. Even if this is not much likely to destroy any data, it cannot be saved, because all data is poured (FLUSH) to disk between each query, and there is never lost data for errors due to errors in MySQL. Myisam Table Processor - Beta This is a new function of MySQL 3.23, which is mostly ISAM table code but there are many new useful features.
Syntax processor and lexical analyzer - stable for a long time without an error report in this system. C Customer Code - Stabilize no known issues. In early 3.20 versions, there are some restrictions on the size of the send / receive buffer. After 3.21.x, the size of the buffer is now dynamic, and can go to a 24m default value. Standard Client - Stabilization These include mysql, mysqladmin, and mysqlshow, mysqldump, and mysqlimport. Basic Structure Query Language - Stable Basic SQL Functions System, String Class and Dynamic Memory Processing, this system is not seen in this system. Query Optimization Program - Stability Range Optimizer - Gamma Join Optimizer - Stable Lock - Gamma This is very dependent on the system, on some systems, locked in standard operating system (Fcntl ()) has a big problem In these cases, you should run the mysql daemon with option -skip-locking. When using NFS mounted file systems, problems are known to appear on SUNOS on some Linux systems. Linux thread - Gamma uniquely discovered problem FCNTL () call, which is resolved by using MySQLD-Skip-Locking options. Some people have reported the lock problem in version 0.5. Solaris 2.5 pthreads - Stabilize us in our development work. Mit-pthreads - Gamma has not reported errors since 3.20.16, and has no known errors from 3.20.16. In some systems, there is a "functional failure" in some operations (1/20 seconds of sleep) in some operations. Of course, Mit-pthreads may make anything slower, but the index-based SELECT statement is usually completed within one time, so there should not be a MUTEX lock / thread trick. Other threads Realization - Alpha - Beta porting to other systems is still new and may have errors, probably in MySQL, but the most common is the thread implementation itself. Load Data ..., insert ... Select - Stable Some people have thought that they have found mistakes here, but these often misunderstand. Please check the manual before reporting the question! ALTER TABLE - Stabilize in a small change in 3.22.12. DBD - Stability is now maintained by Jochen Wiedmann. MySQLACCESS - Stability is written and maintained by YVES Carlier. Grant - gamma mysql 3.22.12. It has made great changes. Myodbc (using ODBC SDK 2.5) - Gamma seems to work very well with some programs. TCX provides email support for paid customers, but the MySQL mailing list usually provides a common question. Errors usually use a patch patch, for a serious mistake, almost always has a new version release. 1.6 Compliance 2000 (Year 2000 Compliance) MySQL itself has no problem in 2000 (Y2K): MySQL uses UNIX time functions and no date in 2069, all 2 years are considered to range from 1970 to 2069, This means that if 01 stored in a year type column, MySQL is used as 2001. All MySQL date functions are stored in a file 'sql / time.cc' and have a careful encoding guarantee for 2000. MySQL 3.22 and later versions, new Year of Year type can store 0 years and 1901 to 2155 in one byte and display them with 2 or 4 digits. You can use a MySQL application in a way that is not Y2k-Safe to go deep into this issue.
For example, many old applications use 2-bit numbers (it has an unisforced) rather than 4 digit storage or operation year, this problem may confuse the application that uses the prompts such as 00 or 99 as a "loss" value. . Unfortunately, these problems may be difficult to repair, because different applications may be written by different programmers, each of which may use different conventions and date processing functions. Here is a simple demonstration, showing MySQL without any dates prior to 2030.
Mysql> Drop Table if EXISTS Y2K; MySQL> Create Table Y2k (Date Date, Date_Time DateTime, Time_stamp TimeStamp); MySQL> Insert Into Y2k Values ("1998-12-31", "1998-12-31 23:59:59 ", 19981231235959); mysql> INSERT INTO Y2K VALUES (" 1999-01-01 "," 1999-01-01 00:000000); MySQL> Insert Into Y2k Values ("1999-09-09" , "1999-09-09 23:59:59", 19990909235959); MySQL> INSERT INTO Y2K VALUES ("2000-01-01", "2000-01-01 00:00:00", 20000101000000); MySQL> INSERT INTO Y2K VALUES ("2000-02-28", "2000-02-28 00:00:00", 20000228000000); MySQL> Insert Into Y2k Values ("2000-02-29", "2000-02-29 00:00:00 ", 2000029000000); MySQL> INSERT INTO Y2K VALUES (" 2000-03-01 "," 2000-03-01 00:00:00 ", 20000301000000); MySQL> Insert Into Y2k Values (" 2000 -12-31 "," 2000-12-31 23:59:59 ", 20001231235959); mysql> Insert Into Y2k Values (" 2001-01-01 "," 2001-01-01 00:00:00 ", 20010101000000); Mysql> Insert Into Y2K Values ("2004-12-31", "2004-12-31 23:59:59", 20041231235959); mysql> Insert Into Y2k Values ("2005-01-01", " 2005-01-01 00:00:00 ", 2005 0101000000); mysql> Insert Into Y2K Values ("2030-01-01", "2030-01-01 00:00:00", 20300101000000); MySQL> Insert Into Y2K Values ("2050-01-01", " 2050-01-01 00:00:00 ", 20500101000000); MySQL> SELECT * from Y2K; -------------------------- ------- ---------------- | Date | DATE_TIME | TIME_STAMP | ------------ ---- ---------------- ----------------
| 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 || 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 || 1999-09-09 | 1999-09 -09 23:59:59 || 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 || 2000-02-28 | 2000-02-28 00:00:00 | 2000028000000 | | 2000-02-29 00:00:00 | 20000229000000 || 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 || 2000-12-31 | 2000-12 -31 23:59:59 | 20001231235959 || 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 || 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 | | 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 || 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 || 2050-01-01 | 2050-01 -01 00:00:00 | 00000000000000 | ------------ ------------------ ---- ------------ 13 ROWS IN SET (0.00 sec) This means that the DATE and DATETIME types will not have any problems in the future (they handle the date to 9999). The TimeStamp type is used to store the current time, there is a maximum of only 2030-01-01. TimeStamp on 32-bit machines (with symbolic values) has a range from 1970 to 2030, which processes time to 2106 (no sign values) on the 64-bit machine. Although mysql is compliant with Y2K, it is your responsibility to provide no input. For the rules of the Mysql handle (including 2 digits year), see 7.3.6.1 Y2K issues and date types. 1.7 SQL General information and tutorials on MySql mailing list, this book is more than recommended: Judith S. Bowman, Sandra L. Emerson and Marcy DarnovskyThe Practical SQL Handbook: Using Structured Query LanguageSecond EditionAddison-WesleyISBN 0-201-62623-3http : //www.awl.com This book also accepted some advice from MySQL users: Martin Gruberunderstanding SQLisbn 0-89588-644-8Publisher Sybex 510 523 8233Alameda, CA USA A SQL language tutorial can be at http: // www. Geocities.com/siliconvalley/vista/2207/sql1.html is found. SQL IN 21 TAGEN (written online with German) http://www.mut.de/leseecke/buecher/sql/inhalt.htm. 1.8 Useful mysql related link 1.8.1 tutorial
A Beginner's Tutoral of How To Start Using MySQL http://www.alysisandsolutions.com/code/mybasic.htm How to install and set Mysql on a Windows machine. Many MySQL tutorials create a BMYSQL-based website mysql perl tutorial php / mysql tutorial 1.8.2 perl related links Mysql's Perl DBI FAQ1.8.3 MySQL Discussion Forum
Use mysql instances (Check TOP 20) 1.8.4 Support Mysql business applications
SupportWizard; Interactive helpdesk on the web (This product includes a licensed copy of MySQL) Right Now Web; Web automation for customer service Bazaar; Interactive Discussion Forums with web interface PhoneSweepT is the world's first commercial telephone scanner a lot in recent years. Intrusion is not through the Internet, but through the illegal dial modem. Phonesweep lets you discover these modems by looping on each number controlled by the phone to your company, Phonesweep has a built-in expert system, which can identify more than 250 kinds of Different types of remote access procedures, including Carbon Copyt, PCANYWHERET, and Windows NT Ras. All information is saved in the SQL database, and finally produces a detailed report, indicating that the service of the dial-up modem in your company is spying. 1.8.5 SQL client
Microsoft Windows Platform Mysql Editor / Utility KDE MYSQL Customer Kiosk: A mysql client for database management is written with Perl, which will become part of Bazaar. 1.8.6 WEB Development Tools Support Mysql
PHP: A server-side embedded HTML script language MIDGARD application server: MYSQL and PHP powerful website development environment SmartWorker is a web application development platform XSP: E (x) TENDIBLE (S) ERVER (P) Ages: is a use Java writes embedded HTML tag language (previous XTAGS is famous) DBSERV: is an expansion of the web server, which will go to the database output into your HTML encoding. You can use any HTML functions in your output, only the client can stop you. It runs as a stand-alone server or as a Java servlet. Chili! Soft platform-independent ASP mysql php demos forwardsql: HTML interface for Mysql database WWW-SQL: Display Database Information MINIVEND: Web Shop Heitml: It is HTML server-side expansion, at the same time is a fourth-generation language language Metahtml: a WWW Application Dynamic Programming Language VelocityGen for Perl and Tcl Hawkeye Internet Server Suite Linux Network Database Connection WDBI: Yes as a very well-supported Mysql database web browser WebGroove script: HTML compiler and server side Scripting Language A server-side website scripting language How to use COLDFusion with ColdFusion using MySQL Manager WebMerger: This CGI tool interprets the file and generates a dynamic output based on a simple label, providing MySQL and PostgreSQL's Ready-to TO -run driver. PHPCLUB: PHP Tips MySQL and Perl Scripts Widgetchuck: Website Tools and Gadgets ADCYCLE: Advertising Management Software 1.8.7 Database Design Tools supported by mysql DEZIGN for Databases is a database development tool (ERD) using an entity relationship chart. An Apache Authentication Module The Roxen Challenger Web Server 1.8.9 Extension to other programs An active code of MySQL Delphi interface. Written by Matthias Fichtner. TMYSQL: A library of library Delphi TDataSet components using mysql via Delphi supports Bind (Internet Name Server) Use MySQL's Sendmail Expansion 1.8.10 Use mysql through other programs Links related to mysql1.8.11 odbc via Access Popular IODBC Drivers (libiodbc) Now get a link related to the FreeoDBC Home 1.8.12 API in an open source code Www.jppp.com: TDataSet compatible parts are implemented for the MySQL section. QPopMysql: A Mysql database is allowed to perform POP3 authentication patches. There is also a connection to the PAUL KHAVKINE PROCMAIL patch, allowing any MTA to deliver letters to users in the MySQL database. The mysql binding SCMDB: SCMDB is a plugin for SCM, SCM transplantation of the C language library to the conceptual framework (Scm). With this library, the frame developer can connect to a MySQL database and use embedded SQL in their block clock. 1.8.13 Other MySQL related links Registry of Web providers who support MySQL Links about using MySQL / MySQL in Japan / Asia Commercial Web defect tracking system PTS: Project Tracking System Job and software tracking system ExportSQL: A script to export data from Access95 SAL (Scientific Applications on Linux) MySQL entry a consulting company which mentions MySQL in the right company PMP Computer Solutions database developers using MySQL and mSQL Little6 Inc An online contract and job finding site that is powered by MySQL, PHP3 and Linux. DELECis A tool which makes it very easy to create an automatically generated table documentation. They have used MySQL as an example. Steve Fambro Uses MySQL and webmerger. there is an employee database, and a license plate database with all of the registered Utah vehicles (over 1.2 million). The License plate field is indexed ..... so the * searches * are instantaneous. World Records A search engine for information about music that uses MySQL and PHP. A Contact Database using MySQL and PHP Web based interface and Community Calender with PHP Perl package to generate html from a SQL table structure and for generating SQL statements from an html form. Basic telephone database using DBI / DBD . Installing new Perl modules that require locally installed modules JDBC examples by Daniel K. Schneider SQL BNF Object Oriented Concepts Inc; CORBA applications w ith examples in source DBWiz; Includes an example of how to manage own cursors in VB Pluribus Pluribus, is a free search engine that learns to improve the quality of its results over time Pluribus works by recording which pages a user prefers among those returned for. a query. a User votes for a page by selecting it; Pluribus then uses that knowledge to improve the quality of the results when someone else submits the same (or similar) query Uses PHP and MySQL Stopbit A technology news site using MySQL and PHP Example scripts at Jokes2000 FutureForum Web Discussion Software http:.. // www.linuxsupportline.com/~kalendar/ KDE based calendar manager The calendar manager has both single user (file based) and multi user (MySQL database) support. Example of storing / retrieving images with MySQL and CGI Online shopping cart system Old Photo Album The album is a collaborative popular history of photography project that generates all pages from data stored in a MySQL database. Pages are dynamically generated through a php3 interface to the database content. Users contribute images and descriptions. Contributed images are stored on the web server to Avoid Storing The in The Database As Blobs. All Other Information Is Stored in The Shared MySQL Server. 1.8.16 General Database Link Database Jum p Site Homepage of the webdb-l (Web Databases) mailing list Perl DBI / DBD modules homepage Cygwin tools UNIX on top of Windows dbasecentral.com;.. Development and distribution of powerful and easy-to-use database applications and systems Tek. -Tips Forums Tek-Tips Forums are 800 independent peer-to-peer non-commercial support forums for Computer Professionals. Features include automatic e-mail notification of responses, a links library, and member confidentiality guaranteed. there are many page using MySql. See some mysql users for details. Send any newly added to this table and plus the MySQL ID (in a "Using Tool" page or similar place). 'Editor in charge: eleg (2001-06-06 19:49)
