Everything about database,how to,example,guide,

1005:Found watch failure
1006:Found database failure
1007:The database already existed,Found database failure
1008:The database is nonexistent,Cutout fails except the database
1009:Cannot delete database file to bring about cutout to fail except the database
1010:Cannot delete data catalog to bring about cutout to fail except the database
1011:Cutout fails except database file
1012:Cannot read the record in taking a system to express
1020:The record already was revised by other user
1021:The space is not worth hard disk the rest,Increase hard disk to be able to use a space please
1022:Key word repeats,Change record failure
1023:The mistake happens when shutting
1024:Read file mistake
1025:The mistake happens when changing a name
1026:Write file mistake
1032:The record is nonexistent
1036:Data table is read-only,Cannot undertake modification to it
1037:Systematic memory is insufficient,Restart please database or restart server
1038:At the memory inadequacy of sort with,Increase sort buffer please
1040:The biggest join that already arrived at a database is counted,Increase a database to be able to use join number please
1041:Systematic memory is insufficient
1042:Invalid leader name
1043:Invalid join
1044:Current user did not visit the attributive of the database
1045:Cannot join database,User name or password are wrong
1048:The field cannot is empty
1049:The database is nonexistent
1050:Data table already existed
1051:Data table is nonexistent
1054:The field is nonexistent
1065:Invalid SQL statement,SQL statement is empty
1081:Cannot establish Socket link
1114:Data list is full already,Cannot hold any records
1116:The data table that open is too much
1129:The database appears unusual,Restart please database
1130:Join database fails,Without the attributive of join database
1133:Database user is nonexistent
1141:Current user has no right to visit a database
1142:Current user has no right to visit data table
1143:Current user has no right to visit data to express medium field
1146:Data table is nonexistent
1147:Did not define the visit limits of authority that the user expresses to data
1149:SQL statement solecism
1158:Network mistake,Occurrence misread by accident,Inspect network join condition please
1159:Network mistake,Read overtime,Inspect network join condition please
1160:Network mistake,Occurrence clerical error by accident,Inspect network join condition please
1161:Network mistake,Write overtime,Inspect network join condition please
1062:Field value repeats,Put in storage fails
1169:Field value repeats,Newer record fails
1177:Open data to express failure
1180:Refer clerical failure
1181:Time roll clerical failure
1203:The biggest join that the link that current user and database establish already arrived at a database is counted,Increase practicable database to join is counted or restart please database
1205:Add lock overtime
1211:Current user did not establish the limits of authority of the user
1216:External key tie examines failure,Newer child souvenir records failure
1217:External key tie examines failure,Delete or revise advocate souvenir records failure
1226:The resource that the resource that current user uses already allowed more than,Restart please database or restart server
1227:Attributive is insufficient,You have no right to undertake this is operated
1235:MySQL version is too low,Do not have this function

...

Use MYSQL backs up into travel database,Have very standard database backup method,Have same idea with other database servers,But had thought,MySQL can have more the backup method of the use file directory of forthright,And had had quickly again.

One, data backup shortcut

The test and verify that did not obtain documentation of government-owned Founder type because of this method,Our of short duration calls an experiment.

Purpose:TestA of database of a Mysql in backup HostA lead plane,In restoring HostB plane

Experiment environment:

Operating system:WinNT4.0,Mysql3.22.34,PhpMyAdmin 2.1.0

Mysql database is installed in HostA and build TestA database
HostB machine installs Mysql database,Without TestA database

Methodological measure:

The PhpMyAdmin that start looks carefully at the database list in HostA and HostB,TestA database is done not have in HostB

The installation catalog of the Mysql in finding HostA,Find database catalog Data

In my experiment environment,This catalog is

C:mYsqldata

Find the subdirectory of corresponding database name

C:mYsqldataTestA

Stickup copy falls to the Data catalog of HostB,It is HostA the document that issues with catalog of HostB Mysql data is same

The PhpMyAdmin of refresh HostB looks carefully at database list,We see TestA has appeared,And make inquiry revise it is normal to wait for an operation,Backup restores to restore to succeed

Trial verdict:The database of Mysql can be saved through file form,Backup,Restore to want to restore corresponding file directory only can,Need not use other tool to back up.

Two, standard method (the government suggests) :

Derive the Mysqldump tool that should use MySQL,Main use is:
Mysqldump [OPTIONS] Database [tables]

If you do not give any lists,Whole database will be derived.

Through carrying out Mysqldump- - Help,The option that you can receive the version of your Mysqldump supports is expressed.

Notice,If you run Mysqldump- - Quick or- - Opt option,Mysqldump will be in derive lade before the result whole result collect in memory,If you are deriving a big database,This will be likely it is a problem.
Mysqldump supports following options:

- - Add-locks

LOCK TABLES increases before every watch derives and later UNLOCK TABLE.(Insert MySQL) quickly to make.

- - Add-drop-table

A Drop Table increases before every Create statement.

- - Allow-keywords

The row name that allowing to found is a keyword.This is accomplished by the method that adds watch name in front of row name.

- C, - - Complete-insert

Use complete Insert statement (the name that use a line) .

- C, - - Compress

If client and server all support,compress,Compress both an all information.

- - Delayed

With INSERT DELAYED the command is inserted row.

- E, - - Extended-insert

Use brand-new much travel INSERT is syntactic.(give out more constrictive and faster parenthesis sentence)

- # , - - Debug[=option_string]

The use of trace program (to debug) .

- - Help

Show one helps a news and exit.

- - Fields-terminated-by= . . .

- - Fields-enclosed-by= . . .

- - Fields-optionally-enclosed-by= . . .

- - Fields-escaped-by= . . .

- - Fields-terminated-by= . . .

These choices and - T choice is used together,And have the meaning with same clause of corresponding LOAD DATA INFILE.

LOAD DATA INFILE is syntactic.

- F, - - Flush-logs

Before beginning to derive,Wash the log file in MySQL server.

- F, - - Force,

Although we are in,error of a SQL gets during a watch derives,Continue.

- H, - - Host= . .

The MySQL server from named leader derives data.Default lead plane is Localhost.

- L, - - Lock-tables.

To begin to derive the lock decides all watches.

- T, - - No-create-info

Do not write a watch to establish information (CREATE TABLE statement)

- D, - - No-data

Any travel information that do not keep a watch.If you are only conceivable of the structure of a watch derive,This is very useful!

- - Opt

With- - Quick- - Add-drop-table- - Add-locks- - Extended-insert- - Lock-tables.

Should be those who read in server of a MySQL to you as far as possible the fastest derive.

- Pyour_pass, - - Password[=your_pass]

The password that when joining with the server, uses.If you are not appointed " =your_pass " part,The password that Mysqldump needs to come from terminal.

- P Port_num, - - Port=port_num

The TCP/IP port name that when joining with a lead plane, uses.(this is used at receiving the leader beyond Localhost repeatedly,Because it uses word of Unix dowel joint.)

- Q, - - Quick

Not amortize inquires,Derive directly to Stdout;Use Mysql_use_result() does it.

- S /path/to/socket, - - Socket=/path/to/socket

When joining with Localhost (it is default lead plane) document of use dowel joint word.

- T, - - Tab=path-to-some-directory

Given to every express,Found file of a Table_name.sql,It includes SQL CREATE to command,With file of a Table_name.txt,It includes data.Notice:When this has the same table machine that runs in Mysqld to guard a process to move in Mysqldump to go up only, just work.The format basis of.txt file- - Fields-xxx and- - Lines- - Xxx option will decide.

- U User_name, - - User=user_name

When joining with the server,The user name that MySQL uses.The Unix that default value is you logs onto a name.

- O Var=option, - - Set-variable Var=option

The value that sets a variable.Likely variable is in by the row below.

- V, - - Verbose

Expatiatory mode.More information that print a program place to do.

- V, - - Version

Information of the version that print and exit.

- W, - - Where='where-condition'

Derive only the record that was chosen;Noticing quote is compulsive!

Noticing quote is compulsive!- - Where=user='jimf'%26quot; %26quot;-wuserid%26gt;1%26quot; %26quot;-wuserid%26lt;1 %26quot;

A backup that the commonnest Mysqldump uses a likelihood to make whole database:
Mysqldump- - Opt Database%26gt;Backup-file.sql

But it comes from the information at a database to enrich another MySQL database to using also is useful:
Mysqldump- - Opt Database | Mysql- - Host=remote-host -C Database

Because Mysqldump derives is complete SQL statement,Used Mysql client program to be able to guide data very easily so:
Mysqladmin Create Target_db_name
Mysql Target_db_name%26lt;Backup-file.sql

...

Of My SQL make mistake acting stopwatch,Be arranged according to the head file Mysql/include/mysqld_error.h of My SQL and become.Wrong place,Ask authority point out mistakes so that they can be corrected.

1005:Found watch failure

1006:Found database failure

1007:The database already existed,Found database failure

1008:The database is nonexistent,Cutout fails except the database

1009:Cannot delete database file to bring about cutout to fail except the database

1010:Cannot delete data catalog to bring about cutout to fail except the database

1011:Cutout fails except database file

1012:Cannot read the record in taking a system to express

1020:The record already was revised by other user

1021:The space is not worth hard disk the rest,Increase hard disk to be able to use a space please

1022:Key word repeats,Change record failure

1023:The mistake happens when shutting

1024:Read file mistake

1025:The mistake happens when changing a name

1026:Write file mistake

1032:The record is nonexistent

1036:Data table is read-only,Cannot undertake modification to it

1037:Systematic memory is insufficient,Restart please database or restart server

1038:At the memory inadequacy of sort with,Increase sort buffer please

1040:The biggest join that already arrived at a database is counted,Increase a database to be able to use join number please

1041:Systematic memory is insufficient

1042:Invalid leader name

1043:Invalid join

1044:Current user did not visit the attributive of the database

1045:Cannot join database,User name or password are wrong

1048:The field cannot is empty

1049:The database is nonexistent

1050:Data table is bad already

1051:Data table is nonexistent

1054:The field is nonexistent

1065:Invalid SQL statement,SQL statement is empty

1081:Cannot establish Socket link

1114:Data list is full already,Cannot hold any records

1116:The data table that open is too much

1129:The database appears unusual,Restart please database

1130:Join database fails,Without the attributive of join database

1133:Database user is nonexistent

1141:Current user has no right to visit a database

1142:Current user has no right to visit data table

1143:Current user has no right to visit data to express medium field

1146:Data table is nonexistent

1147:Did not define the visit limits of authority that the user expresses to data

1149:SQL statement solecism

1158:Network mistake,Occurrence misread by accident,Inspect network join condition please

1159:Network mistake,Read overtime,Inspect network join condition please

1160:Network mistake,Occurrence clerical error by accident,Inspect network join condition please

1161:Network mistake,Write overtime,Inspect network join condition please

1062:Field value repeats,Put in storage fails

1169:Field value repeats,Newer record fails

1177:Open data to express failure

1180:Refer clerical failure

1181:Time roll clerical failure

1203:The biggest join that the link that current user and database establish already arrived at a database is counted,Increase practicable database to join is counted or restart please database

1205:Add lock overtime

1211:Current user did not establish the limits of authority of the user

1216:External key tie examines failure,Newer child souvenir records failure

1217:External key tie examines failure,Delete or revise advocate souvenir records failure

1226:The resource that the resource that current user uses already allowed more than,Restart please database or restart server

1227:Attributive is insufficient,You have no right to undertake this is operated

1235:My SQL version is too low,Do not have this function

...

The Linux that learned for a long time,Be necessary to be done arrange note,The installation that is Mysql below takes notes, with common a few use methods.
Because I like to debug,optimize a system, used the executive efficiency of the program after a few options increase process designing when compiling installation so, I understand some likelihoods wrong, hope everybody points out. Mail:fLashc@21cn.com

? installs Mysql
# Tar Zxvf Mysql-4.0.14.tar.gz -C /setup
# Cd /setup/mysql-4.0.14
# Groupadd Mysql
# Useradd Mysql -g Mysql -M -s /bin/false
# . / Configure- - Prefix=/web/mysql \ appoints installation catalog
- - mode of Debug of Without-debug \ purify
- - With-extra-charsets=gb2312 \ adds Gb2312 Chinese character to support
- - the collection version that Enable-assembler \ uses function of a few character
- - Isam of Without-isam \ take out expressed type support to use Isam to express rarely now is a kind of watch that depends on platform
- - the watch that Innodb of Without-innodb \ take out expresses supportive Innodb is processing of general affairs of a kind of support, suit company level application
- - library of Pthread of With-pthread \ requisition (library of Posix line Cheng)
- - Enable-thread-safe-client \ compiles client end with line Cheng means
- - With-client-ldflags=-all-static \
- - With-mysqld-ldflags=-all-static \ compiles service end and client end with means of pure static state

# Make
# Make Install
# Scripts/mysql_install_db \ makes Mysql user database and watch file
# Cp Support-files/my-medium.cnf /etc/my.cnf \copy configures a file, have Large, medium, three environments issue Small, according to machine function choice, if negative charge is bigger, the memory that can revise a few variable inside uses a value
The Mysqld file that # Cp Support-files/mysql.server /etc/init.d/mysqld \copy starts
# Chmod 700 /etc/init.d/mysqld
# Cd /web
# Chmod 750 Mysql -R
# Chgrp Mysql Mysql -R
# Chown Mysql Mysql/var -R
# Cd /web/mysql/libexec
# Cp Mysqld Mysqld.old
# Strip Mysqld
# Chkconfig- - Add Mysqld
# Chkconfig- - Level 345 Mysqld On
# Service Mysqld Start
# Netstat -atln
Look have the port of 3306 to open, if Mysqld cannot be started, look / below Web/mysql/var make mistake log, it is the issue with catalog good without the setting attributive commonly
# Ln -s /web/mysql/bin/mysql /sbin/mysql
# Ln -s /web/mysql/bin/mysqladmin /sbin/mysqladmin
# Mysqladmin -uroot Password "youpassword" # installs the password of Root account
# Mysql -uroot -p
# inputs the password that you installMysql%26gt;use Mysql;Mysql%26gt;delete From User Where Password=%26quot;%26quot;; # is deleted use at this machine account of faceless connective empty passwordMysql%26gt;flush Privileges;Mysql%26gt;quit

The interface that ? installs DBI and DBD For Mysql to be used at offerring Perl to visit Mysql database is normative, affirm you had installed Perl please, general and acquiescent system mount

# Cd /setup
# Tar Zxvf DBD-mysql-2.9002.tar.gz -C /setup
# Tar Zxvf DBI-1.38.tar.gz -C /setup
# Cd DBI-1.38
# Perl Makefile.PL
# Make
# Make Test
# Make Install
The method because of the Perl in some Perl programs is / Usr/local/bin/perl, and acquiesce of red cap system is / Usr/bin/perl, had better make a connection so, assure compatibility
# Ln -s /usr/bin/perl /usr/local/bin/perl
# Cd. . / DBD-mysql-2.9002
Generate installation to configure a file to need to add a few parameter after Perl Makefile.PL, more troublesome, we can arrive through adding / the join of this program solves Web/mysql/bin/mysql_config problem
The system can search this command automatically to generate the database parameter that installs a need
# Ln -s /web/mysql/bin/mysql_config /sbin/mysql_config
# Perl Makefile.PL
# Make
I always run Make Test here the half makes mistake,But the test scenario that can run Mysql catalog to fall is after installation, the Mysql that does not know 3.x how)
You can run test scenario now, rate is very slow nevertheless, it is good to hang backstage
# Perl /web/mysql/sql-bench/run-all-tests- - User=root- - Password="youpassword "


Add:
A few useful option with ? common Mysql and commandMysqld-----Open log supports
- - Log
- - Log-update
- - Log-long-formart
? MysqlGrant All On Database. * To User Identified By "password" builds one User user, gift all attributive of its Database databaseThe droit that Revoke All On Database From User moves to go up in Database database except User user is restrictedMysql -uroot -p- - One-database Databasename%26lt;File.sql guides from File.sql file Sql statement arrives Databasename in this database
? MysqladminMysqladmin Flush-logs commands Mysqld opens a new log document afresh, it is file of clear sky old daily record, be equivalent to metempsychosis
? Mysqldump
- - Add-drop-table derives statement of Droptable If Exists is added when Sql statement
- - Quick derives quickly
- - Opt optimizes option (recommend)
? Myisamchk
The examination expresses option
- - Extend-check patulous examination
- - an examination option with Medium-check more eclectic career
Repair expresses option
- - Recover rehabilitate expresses a file
- - Safe-recover safe rehabilitate expresses a file, than- - Recover wants slow
Got-up rehabilitate is expressed
- - Force keeps clear of compulsively last temporarily file
- - the content that Quick makes a file according to element only has repair
The management of ? Mysql
The environment that can use Phpmyadmin(to need Php supports) support with Mysqlcc(linux graph interface, WINDOWS system supports)

...

Debug an environment:The installation of system of Redhat9.0 Apache1.3.29 Mysql3.23.58 PHP4.3.4 Linux I was not told,This is basic skill,Actually this article is in the other Linux of similar Redhat also should general,The method that everybody wants to master me to offer only goes.Remember installing Redhat9.0 when the Apache that does not install systematic acquiesce,Mysql and Php and relevant software.Had installed delete the bag that has installed with Rpm -e * please.

1. installs Mysql3.23.58

The Rpm package that actually website of government of Mysql of plainspoken direct installation offers also is a more feasible method,The Rpm of his official website wraps offer be being issued basically with Tar bag is synchronism,At this o'clock I am preferred,The circumstance that Mysql library file won't appear to cannot be found in installing the retrospective that Rpm wraps to debug at least.But here still is necessary to interpret the move that installs from the definition,After all the netizen still says to hold out from what define installation much.

Software is gotten:http://www.mysql.com/downloads/index.html

Install measure:


...

# Cd /usr/src
# Tar -zvxf Mysql-3.22.25-pc-linux-gnu-i686.tar.gz (produces Mysql-3.22.25-pc-linux-gnu-i686 catalog)
# Cd Mysql-3.22.25-pc-linux-gnu-i686
# . / Configure- - Prefix=/usr/local/mysql (among them parameter set purpose installs method)
# Make
# Make Install

When the attention is running Configure,The installation destination route of set MySQL is / Usr/local/mysql,This method is used even in succeed installation process.Next,Build original database:

# Scripts/mysql_install_db

This command will be in / two databases are built below Usr/local/mysql/var/ :Mysql and Test,Among them former the attributive management database that is MySQL,Latter is to let you do what the exercise uses.Notice:If you install fruit MySQL in used,Initiative database has existed,Do not need to be reinstalled again.

Finally,The MySQL that start:
# Cd /usr/local/mysql/bin
# . / Safe_mysqld%26amp;

If you hope linage of voluntary when the machine is started movement occupies a library,serve,Can join the starting command above / in Etc/rc.d/rc.local file.

Now,The data processing that MySQL has run to rose and await you commands!But,Must take care:The Root administrator that you had not been you installs a password,The database that anybody can revise you follow one's inclinationsly (include most main attributive library) !If believing,Make the following exercise (assume our present list is / Usr/local/mysql/bin) :

Try user of try out random to login,Move:

$ . / Mysql -u Root

Can carry software in order to enter %26quot;mysql%26gt;%26quot; client to hint accord with keeps abreast of instantly any data processing are operated and do not need any password all right;This explains,The password that place of Zhang date and your Unix Zhang date uses the administrator "root" of MySQL can be different.To add countersign to Root Zhang date,Carry out:

# . / ' of new word of command of Mysqladmin -u Root Password '

Carry out with average user body again:

$ . / Mysql -u Root

Criterion the system can hint input password,The password is incorrect reject a visit.Actually,Although you are Root user,If ambiguous demonstrate uses a code,The system also is to refuse all the same:

# . / Mysql -u Root

Or

# . / Mysql The system is answered:
ERROR 1045: Access Denied For User: 'root@localhost' (Using Password: NO)

Use only - P parameter,Explain you want to use a code clearly,The system just can hint you input a password,It is when input code is right square accessibility:

# . / Mysql -u Root -pPassword:******** (inputs countersign)
Welcome To The MySQL Monitor. Commands End With; Or G.
Your MySQL Connection Id Is 33 To Server Version: 3.22.25

Type 'help' For Help.

Mysql%26gt;

Key in Exit to return Shell.

Script Mysql.server (in be located in `share/mysql' catalog) be used at starting or stop MySQL server:
Shell%26gt;Mysql.server StartShell%26gt;Mysql.server Stop

This script starts a server through carrying out Safe_mysqld actually.Stop a server to also can come true through running a program:
Mysqladmin Shutdown

Also can join `/etc/rc.local' file through will undermentioned command,Start MySQL automatically when the system is started:

/ Bin/sh -c 'cd /usr/local/mysql; . / Bin/safe_mysqld %26amp;'

...

A lot of use a project, all sorts of functional requirement basically can come true with MYSQL database when just starting,As the grow in quantity that uses an user,The addition of data bulk,MYSQL appears gradually the circumstance that can'ts bear heavy burden:Join delays very slow even machine,Have then to the demand of ORACLE from MYSQL change data,Applied process also should be done accordingly a few revise.Oneself sum up a the following note,The hope is helped somewhat to everybody.

1 .The data type that grows automatically is handled
The data kind that MYSQL has automatic growth,This field need not be operated when inserting a record,Can obtain data automatically to be worth.The data type that ORACLE did not grow automatically,The alignment order that needs to build one each to move growth,The next value that an alignment name wants when inserting a record is endowed with hereat field.

The name of number of serial of CREATE SEQUENCE (had better be watch name + serial number labels) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;
Among them the greatest value is decided by the length of the field, if the serial number NUMBER(6) of the automatic growth of the definition, maximum is 999999
INSERT statement inserts this field value to be: The name.NEXTVAL of serial number

2.The processing of only quote
String can have with double quote bag in MYSQL,A string can be wrapped with only quote only in ORACLE.Replace in what only quote must make before insert and altering string:Replace an only quote that all occurrence into two only quote.

3. Turn over the processing of the SQL statement of the page
The SQL statement that MYSQL processing reverses a page is simpler,Begin the position with LIMIT, record a number;The place that result market still can reach with SEEK fixed position in PHP.The SQL statement that ORACLE processing reverses a page is trivaller.Every result collect has field of a ROWNUM to indicate only its position, and can use ROWNUM%26lt;100 only, cannot use ROWNUM%26gt;80.
It is two kinds of better ORACLE after the course is analysed below statement of SQL turning over a page (the field name that ID is only key word) :
Statement one:
SELECT ID, [FIELD_NAME, . . . ] FROM TABLE_NAME WHERE ID IN (SELECT ID FROM (SELECT ROWNUM AS NUMROW, 2) WHERE NUMROW of condition of 1 ORDER BY of condition of ID FROM TABLE_NAME WHERE%26gt;80 AND NUMROW%26lt;3; of condition of 100) ORDER BY

Statement two:
SELECT * FROM ((SELECT ROWNUM AS NUMROW, c. * From (select [FIELD_NAME, . . . ] 2) C) WHERE NUMROW of condition of 1 ORDER BY of condition of FROM TABLE_NAME WHERE%26gt;80 AND NUMROW%26lt;3; of condition of 100) ORDER BY

4 .Grow the processing of string
The processing ORACLE of long string also has its special place.Length of string of the biggest exercisable is less than when INSERT and UPDATE be equal to 4000 monomial sections, if want to insert longer string, consider a field please with CLOB type,The method uses the DBMS_LOB course that takes oneself in ORACLE to wrap.Before inserting modification record, must do undertake sky and length are judged is not,Cannot be worth for empty field and exceed length field value to should put forward to warn, return operate last.

5. The processing of date field
DATE of cent of MYSQL date field and TIME two kinds,ORACLE date field has DATE only,Contain information of date time second,The systematic time that uses current database is SYSDATE, accurate to the second,Perhaps change into date function TO_DATE(with string ' 2001-08-01 ' , ' YYYY-MM-DD ' ) year - month - : ?ind the format YYYY-MM-DD HH24:M of ? secondI:SS TO_DATE() still has pattern of a lot of kinds of date, can consult ORACLE DOC. Date field is changed into string function TO_CHAR(' 2001-08-01 ' , ' YYYY-MM-DD HH24:MI:SS ' )

The mathematical operation formula of date field has very big different.MYSQL is found from present time 7 days use DATE_FIELD_NAME%26gt;SUBDATE (NOW () ,INTERVAL 7 DAY) ORACLE is found use DATE_FIELD_NAME %26gt;SYSDATE - 7; 7 days from present time

6. The processing of empty character
The is not empty field to also be free content of MYSQL,Blame empty field was defined to not allow to praise available content in ORACLE.The NOT NULL that presses MYSQL will define ORACLE to express a structure, the mistake can arise when derivative is occupied.Because this guides want to undertake to empty character when data,judge,If be NULL or empty character,The string that needs to change it into a blank space.

7.The faintness of string is compared
Field name is used in MYSQL Like '% string %' , field name also can be used in ORACLE %' of Like '% string but this kind of method cannot use index, speed is unhappy,Compare name of function Instr(field with string, ' string ')%26gt;0 can get more accurate search a result.

8.In program and function,Result collect and finger notice please after the work that handles a database is finished release.

...

The article described MySQL,One kind uses tripartite database to develop electronic commerce and other the effective tool of complex, dynamic website.The SQL server that MySQL is Cheng of a kind of fast, multi-line and complete function.Besides the basic architecture that describes MySQL system,The article still offerred the simple give a demonstration that writes with Tcl and C++ ,The Web application that assists you to develop supportive database.

An applied process that must store or visits a large number of information can benefit a lot from inside using product of tripartite database.When undertaking, must be in the visit to information on the many example of the program is such more.The application that is based on Web (include electronic trade) be it is good and paradigmatic.

Why to use independent database?

Web server must make its handle script to method will store about the state information that after offerring its, visits.Although use a few more primitive method likely- - for example dump confuses your database to what text file or development abstain- - but all services that only mature database application ability provides more complex Web application place to need.Because have a few,the software package that acquires freely can be used at this purpose,Write so custom-built do not have too auspicious place on database engine of application especially.Additional,In using tripartite database to still make Web developer need not throw development and the job that maintain a database.

MySQL database

Compile through using script language make peace model systematic language (for example C) ,the database compositive it is easy to comparative possibly to Linux application.The MySQL that can obtain freely (issue below GNU Public License) the database provided a series of complex SQL functions,And easily compositive in application.MySQL is fast, of multi-line Cheng,Support ANSI and ODBC SQL level.Add software of on tripartite,The watch of the clerical security that MySQL uses at general affairs to handle manage application with respect to support.

Note:What is clerical processing?

General affairs is the logarithm that need carries out with atomic means do according to library place a series of changing.Their or must be carried out entirely,Or is not carried out.For example,When the product is sold on Web all indispensible databases change composition a general affairs.

Database need subtracts at the same time client account remaining sum and product inventory,Fail otherwise and an operation is not carried out.

Why to no matter the server is stemmed from,plant reason happening breaks down should not cause general affairs to be carried out by the part.For example bill calculates more, the product does not have consign,Or inventory is disloyal the result that waiting is the work that completes partly likely.

The database that supports clerical processing is OK in enclosing code of a group of databases in a general affairs,Any failure during general affairs is carried out can let a database answer the condition before rolling general affairs to begin.

This is the daily record that handles through maintaining all databases,And of the deputy original implementation that its savagery expresses,Allow to answer when the server is started afresh the next time after failure roll an operation.The expense on this kind of time and space is clerical safety a database system place is indispensible kind eclectic.

Onefold MySQL server is controlling a series of databases,They can be visited through the server with similar pattern.The watch that every database is a group of aleatoric amounts actually,The user of concept and other SQL database is similar.Every watch lists composition by the data that takes a kind.Data can be integer, real value, string or other type,Include primitive binary system to flow.Expressing medium every group is a memory record in the database.

MySQL is designed and construction becomes server of client computer / .Server Mysqld can be in can move from any machines that Internet visits (had better be with Web server on same table or a the most adjacent machine,Answer time reasonably in order to ensure) .MySQL client computer uses a request to be contacted with MySQL server,The database that revise or inquires server place to have.In the Web application program that supports a database,Database client computer is Web server or the CGI script that arise by Web server.These client computer can use high-class script language or elementary system language is written,The database API that should be put in this kind of language only can.In Linux,Most script language is with what C comes true,Because of existence MySQL C API,The script language that should add MySQL support so or tool should be very easy.Majority script language had completed this one step.

MySQL API

MySQL API is usable at all sorts of languages,Include almost all the language that writes place of website back end to be used actually.Use these API,We are OK compose is built by client computer of MySQL of Web server pilot.

API (use at the database to visit) work in order to be based on connective mode.The first thing that client computer must do is to open the join with MySQL server.The user name that this includes to use a server to know appropriately and countersign will undertake identity attestation to join.After establishing link,The specific database that the server chooses to want to use.After deciding initialization,Process of client computer application (script of server square CGI is for us) undertake with one of two kinds of means with respect to can free land and database alternant:Can run groovy SQL to command,Include to add and delete a watch,And add a record to them;Also can run inquiry to returning the database of the result.The record that inquiry makes group and one inquiry match,Next,Client computer is OK one by one visits a record,Until examine all records,Or the record that client computer cancels of short duration to hang retrieves.After the operation that once script finished logarithm,occupies a library,Be shut with the join of the server.

The website that wants compose to build compositive database to visit,Need writes CGI script to make dynamic kill according to database condition.Web server starts CGI script,In outputting proper formatted HTML their standard output to flow next.A client computer delivers it after Web server catchs HTML,As the request be undertake to static HTML page in that way.In the course that builds HTML,Script can revise a database,Also can inquire and incorporate the result in their output.

As a give typical examples that explains afore-mentioned processes simply,The code below (write with C and Tcl) the database of the product detailed account that inquiry includes some company supply and marketing to make work.All character that this did not use MySQL API of two kinds of languages absolutely,But offerred simple and easy and fast, patulous give a demonstration,Can carry out any SQL to command to database content.In this in,Script showed all products under specific price.In practice,The user may input this price in Web browser,Send a server it next.Our leave out the detail that undertakes from inside environmental variable fetch decides HTML expresses only cost reading,Do not have what difference because of it and the circumstance that in supporting the CGI script of the database, carry out.For clear for the purpose of,We were installed beforehand suppose a few more specific parameter (the price that wants inquiry for example) .

The following code is to use what the Tcl Generic Database Interface that obtains freely comes true with Tcl.The advantage of a kind of such interface depends on Tcl is an explanation model,Can undertake be developed quickly and be modificationed quickly to code.

Tcl give typical examples


...

? ? if you are a cycle racing hand and can change instantly by pushbutton engine and do not need to drive the car in the garage to change,That meeting is how to feel?MySQL database is what development staff place does to be like is to press pushbutton to change engine;It lets you select database engine,Come to your simple way switch it.

Of ? ? MySQL taking engine oneself was to be used quite for certain,But below some circumstances,Other engine may want to suit to finish the job more than what at hand uses.If be willing,The database engine that you can use MySQL++ API to found your even,Resemble hitting the carburettor that wears air cylinder him mount.Let us see you how should select engine now,And how be between usable engine switch.

? ?Select your engine

When MySQL is being installed, the database engine that you can use ? ? depends on is how be compiled.Should add a new engine,Must compile MySQL afresh.The concept that compiles applied process to add a character merely may be very strange to the development staff of Windows,But in UNIX world,This had made a standard.Below default circumstance,MySQL supports three engine:ISAM, MyISAM and HEAP.Additionally two sort InnoDB and Berkley (BDB) ,Often also can use.

? ?ISAM

? ? ISAM is a definition is made clear and the data table of trial of time of all previous classics manages a method,The frequency that when it is being designed, is inquired considering the database wants ambitious at updated number.Because of this,ISAM is carried out read the rate that takes an operation very fast,And do not take up many memory and memory resource.Of two of ISAM main inadequacy at at,It does not support general affairs to handle,Also cannot enough fault tolerance:If your hard disk broke down,So data file cannot restore.If you are using ISAM in process of crucial task application,That often must back up you all real time data,Duplicate through its characteristic,MySQL can support such backup application program.

? ?MyISAM

The ISAM patulous format that ? ? MyISAM is MySQL and default database engine.A large number of functions besides the reference that provides the place in ISAM to do not have and field management,The mechanism that MyISAM still uses lock of a kind of form to decide,Will optimize many concurrent read keep an operation.Its cost is you need to often run OPTIMIZE TABLE to command,The space that will restore to be wasted by newer mechanism place.MyISAM still has a few to expand usefully,The MyISAMChk tool that uses repair database document for example and with the MyISAMPack tool that will restore to waste a space.

? ? MyISAM emphasized fast speedread taking an operation,This may be why the main reason that MySQL got Web develops such favour:The mass data operation that in Web development you have is to read take an operation.So,Provider of most fictitious lead plane and Internet platform provider (Internet Presence Provider,IPP) allow to use MyISAM form only.

? ?HEAP

? ? HEAP allows to be stationed in those who stay in memory only temporarily form.Halt stay in memory to let HEAP want to compare ISAM and MyISAM fast,But the data that it manages is not stable,And if closing,did not undertake saving before machine,So all data can be lost.When data is deleted all right,HEAP also won't waste much space.HEAP form needs to use SELECT expression to choose and be held in you when dominating data very useful.Should remember,Delete form after gone form.Let me again come again:Give out in you after form,Do not forget delete form.

? ?InnoDB and Berkley DB

? ? InnoDB and Berkley DB (BDB) the direct product that database engine is the technique that trains MySQL flexibility,This technology is MySQL++ API.When using MySQL,Each challenge that you face results from almost ISAM and MyISAM database engine do not support clerical processing to also do not support ab extra key.Although want to compare ISAM and MyISAM engine a lot of slower,But the support that InnoDB and BDB included pair of general affairs processing and ab extra key,It is place of before two engine these two bits those who do not have.The place before be like is narrated,If your design needs these character medium one person both perhaps,One of two engine after then you are about to be forced to use.

If ? ? feels him really the technology is excellent,The database engine that you still can use MySQL++ to found your.The function that this API offerred operation field, record, form, database, join, safe account for you,And build such as MySQL what such DBMS place need is all and other countless functions.The limits that explained API to had exceeded the article deep,But the presence that you need to understand MySQL++ reachs his the technology of exchangeable engine backside,This are very important.The model that estimates engine of database of type of this plug-in unit can be used to even the XML that founds this locality for MySQL is offerred implement (XML Provider) .(any MySQL++ that read the article develop personnel to be able to regard this as is a requirement.)

...

Express an operation more

In a database,The likelihood is put in many watches,These watches are mutual correlation.We continue to use the case in front.A few basic information that stuff contained in the list that builds in front,Like full name, sexual distinction, give unripe date, birthplace.We found a watch again,The article that this watch uses at describing stuff place to publish,Content includes title of author full name, article, publish date.

1, the content that examines Mytable of the first watch:


...