Frequently Asked Questions about mysql. $Revision$
This document contains answers to some of the most frequently asked
questions about mysql. They're all good questions, but they come up
often enough that substantial net bandwidth can be saved by looking here
first before asking. And some of them are question I think somebody should
ask..
The latest version of this FAQ can be found at http://www.tcx.se/
This FAQ is currently available in TeXInfo, ASCII, Info, Postscript and HTML versions.
The primary document is the TeXInfo file. The HTML version is
automatically produced with texi2html. The ASCII and info version is
produced using makeinfo. The Postscript version is produced using
texi2dvi and divps.
If you have any suggested additions or corrections, please send them to
the mysql mailing list <mysql@tcx.se> with a subject line of the
form FAQ Suggestion: [Insert Topic Here].
This FAQ is written and maintained by David Axmark and Michael (monty) Widenius.
mysql mailing list.
Requests to be added or dropped from the mysql list should be sent to
the electronic mail address mdomo@tcx.se. Sending a one line
message saying either subscribe mysql or un-subscribe mysql
will suffice. If your reply address is not valid you may use subscribe
mysql your@address.your-domain or un-subscribe mysql
your@address.your-domain.
Please do not send mail to mysql@tcx.se since any mail sent
to this address is automatically forwarded to hundreds of other users.
Your local site may have many subscribers to mysql. In that case, it
may have a local mailing list, so that a single message from tcx.se
is sent to the site and propagated to the local list. In this case please
contact your system administrator to be added to or dropped from the local
mysql list.
Mail to mdomo is handled automatically by majordomo.
Before you ask a question of the mailing list it's a good idea to check
in the manual. If you can't find an answer in the manual, check with your
local mysql expert. If you don't have any luck there, read through this
FAQ. If you still don't have an answer to your question go ahead and send
mail to mysql@tcx.se.
I think I have found a bug. What information do you need to help me?
In both these cases try to specify (if relevant):
mysql. You can find out which version you are
running by typing mysqladmin version. If you know which distribution
file file you used add that (for example mysql-3.20.0.tgz).
uname -a.
When answers are sent to you individually and not to the mailing list, it is considered good etiquette to summarize the answers and mail them to the mailing list.
Try to make your answer broad enough that people other than the original poster may benefit from it. If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who asked. In this case, please make sure that your answer is not a duplication of a previous answer.
Try to summarize the essential part of the question in your reply, but don't feel obliged to quote the whole question.
mysql?
mysql is a SQL (Structured Query Language) database
server. mysql was written by Michael (monty) Widenius. Se the CREDITS
file in the distribution for more credits for mysql and related
things.
mysqlOur first intension was to use mSQL to connect to our own fast low level (ISAM) tables. After some testing we came to the conclusion that mSQL wasn't fast or flexible enough. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL. This API was choosed to encourage code sharing.
Where the name mysql comes from is not perfectly clear. Our base directory and lots of librarys and tools has had the profix my for well over 10 years. But Monty's daughter (some years younger) are also called my. So there the names comes from is not perfectly clear.
mysql version?You can always check http://www.tcx.se/ for the latest version of mysql.
mysql?On the Internet try using a web browser to http://www.tcx.se/
Or ftp to ftp://ftp.sunet.se/pub/unix/databases/relational/mysql/. That isSunet's (Swedish University Network) FTP archive in Sweden.
mysql in source form or binary?
If you want to read (and/or modify) the C and C++ code that makes up
mysql you should always get a source distribution. The code is
always the ultimate manual. The source distribution also contains more tests
and examples.
For most people who want to run mysql on a platform that has binary
releases, a binary version of mysql is more convenient. But tcx's binary
release is not compiled in the same way as the source release so some differences
in where support files are located exist.
We use GNU autoconf so it will be possible to port to all modern systems with working Posix threads and a C++ compiler. The client code does require C++ but not threads. We use the software ourselves primarily on Solaris (currently 2.5.1) and some on Linux (currently 2.0.27).
A working Posix thread library is needed for the server. On Solaris 2.5 we use SUN PThreads (the thread support in 2.4 and before is not good enough) and on Linux we use LinuxThreads by Xavier Leroy (Xavier.Leroy@inria.fr).
We also provide a patched version of Provenzano's Pthreads from MIT (see http://www.mit.edu:8001/people/proven/pthreads.html) for someoperating systems that doesn't have posix threads. This will be included in the source distribution, but the source and patch can also be fetched separately.
We have alose tried to use another user level thread package named FSU Pthreads (see http://www.informatik.hu-berlin.de/~mueller/pthreads.html).
This didn't work with mysql because of problems with the implementations. See the thr_lock and thr_alarm programs in the mysys directory for some tests/examples of these problems. More information can be found in the `PORTING' file in the distribution.
mysql support.
mysqld can give error messages in the following languages: English
(default), Germany and Swedish.
To start mysqld with a language use the --language (-L) switch:
mysqld --language swedish
mysqld --language /usr/local/share/swedish
The language files are located (by default) in
`mysql_base_dir/share/LANGUAGE/'
We are going to use the following policy when updating mysql:
Each minor patch made will increment the last number in the version string. When there is new features or minor incompatibilities with previous versions the second number in the version string will get incremented.
Unireg is our tty interface builder, but it uses a low level connection to our NISAM (with is used by mysql) and because of this it's very quick. It has existed since 1979 (on unix in C since ~1986).
It has the following components:
We update most our production databases with the UNIREG interface and serve web pages through mysql (and in some extreme cases the UNIREG report generator).
Unireg takes about 3M of disk space and works on at least the following platforms: SUN OS 4.x, Solaris, Linux, HPUX, ICL Unix, DNIX, SCO and MSDOS.
Unireg is currently only available in swedish and Finnish.
The price tag for UNIREG is 10,000 swedish kr (about 1500$ US), but this includes support. UNIREG is distributed as a binary. (But all the isam source can be found in mysql). Usually we compile the binary for the customer at their site.
New development is concentrated to mysql.
Basic licensing issues:
For more information se the rest of this chapter and the file `PUBLIC' in the distribution.
mysql cost?
For normal use mysql costs nothing. When you sell mysql
directly or as a part of another product you have to pay for it. See the
file `PUBLIC' in the distribution.
The client access part of mysql is in the public domain. The command line client inlcudes parts that is under the GNU Public Licence (readline).
The current price is $200 for one licence and $1000 for 10 licences if payed for at the same time.
If you have a low margin high volume product you can always talk to us about other terms.
A licence includes really basic support. This means that we are trying to answer any relevant question. If the answer is in the FAQ, we are going to redirect the answer to the FAQ. If you do not have a licence/support we will probably not answer at all.
If you discover what we consider a real bug, we are likely to fix it in any case. But if you pay for support we will notify you about the fix status instead of just fixing it in a later release.
More comprehensive support will be sold separately. One year of basic email support costs $200. One year of more extensive email/telnet support costs $2000.
Well currently we can take SWIFT payments or cheques. We are working on payment by credit card but that will take a some time. Se the contact information.
We will try to make a ssl-web server in the future for ordering of mysql things.
Payment should be made to:
Postgirot
110 00 STOCKHOLM, SWEDEN
SWIFT address: PGSI SESS
Account number: 96 77 06 - 3
Specify: license and/or support and your name and email address.
In europe and japan you can use EuroGiro to the same account.
If you want to pay by cheque make it payable to "Detron HB". And mail it to the address below.
Detron HB David Axmark Kungsgatan 65 B 753 21 UPPSALA SWEDEN
In a couple of weeks we hope to make all this much easier by allowing credit cards.
For commercial licensing or if you have any questions about any of the information in this document, please contact:
Detron HB David Axmark Kungsgatan 65 B 753 21 UPPSALA SWEDEN Voice Phone +46-18-10 22 80 Fax +46-8-729 69 05 (I prefer email if possible) E-Mail: mysql-c@detron.se
mysql use?
There are four different copyright's on the mysql distribution.
mysql specific source needed to make the mysqlclient
library and programs in the `client' directory is in the public
domain. Each file which is in the public domain has a header which clearly
states so. This is everything in `client' directory and some parts of
mysys, mystring and dbug libraries.
mysql server is AGPL. See the file
PUBLIC for more info.
Our philosophy behind this is:
mysql
by purchasing a support license.
This is a clarification of the information in the `PUBLIC' file.
mysql may be *used* freely, including by commercial entities for evaluation or unsupported internal use. However, *distribution* for commercial purposes of mysql, or anything containing or derived from mysql in whole or in part, requires a written commercial license from TcX AB, the sole entity authorized by to grant such licenses.
You may not include mysql "free" in a package containing anything for which a charge is being made exept as noted below.
The intent of the exception provided in the second clause is to allow commercial organizations operating an FTP server or a bulletin board to distribute mysql freely from it, provided that:
If you want to distribute software in a commercial context that incorporates mysql and you do *not* want to meet these conditions, you should contact TcX AB to find out about commercial licensing. Commercial licenses involve a payment, and include support and other benefits. These are the only ways you legally can distribute mysql or anything containing mysql: either by distributing mysql under the requirements of the FPL, or by getting a commercial license from TcX AB.
I'm selling a product that can be configured to use mysql although my customer is responsible for obtaining/installing mysql (or some other supported alternative). Does one of us owe you money if my customer choses to use mysql?
If your product REQUIRED mysql to work you would have to pay a licence. If mysql just added some new features it should fall inside normal use. For example is using mysql added logging to a database instead of a text file it should not require a licence. This would of course mean that the user has to fetch and install mysql by himself. If the program is (almost) useless without mysql you would have to get a mysql licence to sell your product.
Do I have to get a licence for my copy?
No you are not selling mysql itself. But is this case we would like you to purchase mysql support. That is either your support of mysql or our support of you (the later is more expensive since our time is limited).
We may choose to distribute older versions of mysql with the GPL in the future. However these versions will be identified as "GNU mysql". Also all copyright notices in the relevant files will be changed to the GPL.
CREATE TABLE supports the PRIMARY KEY (columns),
INDEX (columns) and UNIQUE (columns) syntaxes. A PRIMARY
KEY is always UNIQUE. A key may be a prefix of a CHAR field.
isamchk).
select
(column1+column2) from table
sum, max, min, avg
& count).
show tables, show keys from table and
show columns from table
INSERT,UPDATE and DELETE returns how many rows was
affected.
= and <> to constant NULL
is identical as the IS NULL, IS NOT NULL tests. This is an SQL
extension to be compatible with mSQL. (Of course one should use IS NULL..)
-? or --help as help.
The following column types are supported:
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
TIMESTAMP
FIXED LENGTH STRING
VARIABLE LENGTH STRING
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
A binary large object which can hold any amount of data :) There are 4 kinds of blobs See section What column (column) types are available with mysql?.
There are some constraints because of the message buffer (currently 16384,
but may be changed by the client) and the available memory. To change the
buffer length in mysqld use mysqld -O
max_allowed_packet=max_blob_length, but remember that this is alloced by
each thread!
myODBC defines BLOB:s as LONGVARCHAR.
Restrictions for blobs:
SELECT id,SUBSTR(blob,1,100) group by 2
The following are useful extensions that mysql that you probably won't find in other SQL:s. Be warned that if you use this your code won't be portable to other SQL servers.
SELECT statement. See section SELECT syntax
INTO OUTFILE in a SELECT statement. See section SELECT syntax
KEY or
INDEX in a CREATE TABLE statement. See section CREATE TABLE syntax.
DROP column or CHANGE column in a ALTER TABLE
statement. See section ALTER TABLE syntax
LOAD DATA INFILE. This syntax is in many cases compatible with
Oracles LOAD DATA INFILE. See section LOAD DATA INFILE syntax
" instead of ' to enclose strings.
\ character.
=, <>, <= ,<, >=,>, AND,
OR, or LIKE in a column statement
REGEXP or NOT REGEXP.
MIN() or MAX() as functions, not group functions.
CONCAT() with more than 2 arguments. mysql can take any
number of arguments.
BIT_COUNT(), ELT(), FROM_DAYS(), FORMAT(),
IF(), PASSWORD(),
PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(),
or WEEKDAY().
Se the file `INSTALL-SOURCE' in the mysql distribution.
Check the log file to see if mysqld started up correctly
cd <localstatedir default /usr/local/var> tail <your host name>.log
To verify that mysql is working run the following tests:
> cd /usr/local/bin > ./mysqlshow +-----------+ | Databases | +-----------+ | mysql | +-----------+ > ./mysqlshow mysql Database: mysql +--------+ | Tables | +--------+ | db | | host | | user | +--------+ > ./mysql -e "select host,db,user from db" mysql +------+--------+------+ | host | db | user | +------+--------+------+ | % | test | | | % | test_% | | +------+--------+------+
You can also run the test in the test subdirectory. To run `auto_increment.tst':
./mysql -vf test < ./tests/auto_increment.tst
Expected results are shown in the file `./tests/auto_increment.res'
To startor stop mysql you may use the following commands:
scripts/mysql.server stop scripts/mysql.server start
You will probably want to add these start and stop commands to the appropriate places in your /etc/rc* files when you start using mysql for production applications.
According to my experience it would would just take a few hours to convert a tool using the mSQL C API to the mysql C API.
The procedure:
MYSQL as a connection type (mSQL uses an int)
mSQL are the mysql client/server communications protocols?Enough different that it's impossible (at least not easy) to support both.
The biggest differences between mysql and mSQL:
I don't think it makes sense to give a full spec, as we have plans to optimize this in the near future.
mysql & mSQL 2.0?CREATE TABLE
mysql
mSQL
mysql
CREATE TABLE. Indexes can not be
removed without recreating the table. See section ALTER TABLE syntax
mSQL
CREATE INDEX clause. Indexes
may be removed with DROP INDEX.
mysql
autoincrement as a column type specifier. The given identifier
may be retrieved after update by the API function mysql_insert_id().
mSQL
group by clause)
mysql
count(), avg(), min(), max() and
sum(). min() and max() may take string
arguments. count(*) is optimized to return directly if no other
column is used.
mSQL
group by yet.
mysql
mysql uses indexes if
the like argument doesn't start with a wild-card.
mSQL
mysql
mSQL
mysql
mSQL behavior in mysql, use braces:
select * from table where a=1 and b=2 or a=3 and b=4 -> select
* from table where (a=1 and (b=2 or (a=3 and (b=4)))).
mSQL
mysql
mSQL
mysql
mSQL
mysql
mSQL
mysql
mSQL
having clause.
mysql
SELECT COUNT(*)
AS id_count,id FROM groups GROUP BY id HAVING id_count > 10.
mSQL
mysql
mysql database: user, host and
db which may be used to allow a user per host and database different
privileges. The grant-able privileges on rows are: select, insert, update and
delete. The table and database privileges are: create and drop. Other
privileges in the right to use: shutdown, reload and get the current process
list. @xref{}.
mSQL
Entry level SQL92. ODBC level 0-2.
CREATE INDEX. This only checks if the index exists. You should create
your index with CREATE TABLE.
DROP INDEX. This always succeeds.
mysql use memory ?You can get the currently used buffer sizes with:
> ./mysqld --help
This should result in a all mysqld options and a list of configurable variables like the following
this. See section How can I change the buffer sizes of mysqld ?.
Possibly variables to option --set-variable (-O) are:
keybuffer current value: 1048568
max_allowed_packet current value: 65536
net_buffer_length current value: 16384
max_connections current value: 128
table_cache current value: 64
recordbuffer current value: 524280
sortbuffer current value: 2097144
mysqld one can specify a key buffer. This will buffer
all keys in all tables at FIFO basis. (variable keybuffer)
mysql the easy way: Do a full
join and a sort on two big tables :-(. In the future we will handle this by
after some given memory size limit having the hash heap automatically change to a
file based NISAM table. Now you know how to fill a disk in the future
:-). In many cases mysql can optimize the query so it doesn't need a
temporary table, but one case where this can't be done is when sorting and
grouping on different things.
mysqladmin reload closes all tables that are not used and marks all
used tables to be closed when the running thread finishes. This will
effectively free most used memory.
NOT NULL if possible. It makes everything quicker (and you save 1
bit per column).
MEDIUMINT is often better than INT.
VARCHAR columns a fixed size record format will
be used. This is much quicker (but of course it wastes some
space). See section What is the different row formats? Or when to use VARCHAR/CHAR?.
isamchk -a on the table
once it is loaded with relevant data. This updates a value for each
index that tells how many rows that have the same value for this key in
average. Of course this is always 1 for unique indexes.
isamchk -Sir1
(if you want so sort on index 1). If you have a unique key that you want
to read all records from in numeric order this is a good way to make
that faster.
To check how you are doing, run isamchk -evi on the .ISM file.
The time to insert a record consists of:
Where (number) is proportional time. Here is not calculated the initial overhead to open tables (which is done 1 time for every simultaneous running query).
The size of the table slows down the insert of keys with N log N (Btrees).
(Incomplete, mysql does a lot of optimizations)
((a AND b) AND c OR
(((a AND b) AND (c AND d)))) -> (a AND b) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5 -> b>5 AND b=c
(b>=5
AND b=5) OR (b=6 and 5=5) or (B=7 and 5=6) -> B=5 or B=6
=, >, >=, <,
<=, BETWEEN and a like with a character prefix like
'something%'.
AND levels and key parts that don't:
key = 1 or A = 10 -> NULL (Can't use key)
key = 1 or A = 10 and key=2 -> key = 1 OR key = 2
key_part_1 = const and key_part_3 = const -> key_part_1 =
const
const_table.key = constant
const_table.key_part_1 = const_table2.column and const_table.key_part_2 = constant
ORDER BY or GROUP on columns
from one table and the sort and group are the same, or only one of sort and
group is given, then the sorted table is preferred first in join cue.
mysqld ?
With the mysqld -O variable=size command. Example run:
> mysqld --help (or > mysqld -?)
>mysqld --help
/my/monty/sql/mysqld Ver 3.20.5-beta for SOLARIS 2.5 (SPARCstation)
TCX Datakonsult AB, by Monty. This is FPL, not free software
This software comes with NO WARRANTY: see the file PUBLIC for details.
Starts the mysql server
Usage: /my/monty/sql/mysqld [OPTIONS]
-b, --basedir=path path to installation directory
-h, --datadir=path path to the database root
-#, --debug=... output debug log. Often this is 'd:t:o,filename`
-T, --debug-info print some debug info at exit
-?, --help display this help and exit
-L, --language=... client error messages in given language
-l, --log[=filename] log connections and queries to file
--log-isam[=filename]
log all isam changes to file
-O, --set-variable var=option
give a variable an value. --help lists variables
--skip-new-routines don't use new possible wrong routines.
--skip-grant-tables start without grant tables. This gives anyone FULL
ACCESS to all tables!
--skip-locking don't use system locking. To use isamchk one has
to shut down the server.
--skip-unsafe-select skipp unsafe select optimizations.
-V, --version output version information and exit
Current base_dir: /my/monty
Current data_dir: data/
Current language: english/
Possibly variables to option --set-variable (-O) are:
keybuffer current value: 1048568
max_allowed_packet current value: 65536
net_buffer_length current value: 16384
max_connections current value: 128
table_cache current value: 64
recordbuffer current value: 524280
sortbuffer current value: 2097144
> mysqld -O keybuffer=100k -O sortbuffer=100k -O recordbuffer=100k
mysqld demon starts with a cd to 'mysql-data-dir'. After this
mysqld-data-dir is changed to './' (current dir). All paths
(databases, pid file, and log file) are prefixed with './'
mysql to run at full speed?
mysqld -O keybuffer=16m -O sortbuffer=1m -O recordbuffer=512k --skip-locking
--skip-locking disables file locking between SQL requests. If this is
used then the following can happen:
mysqld crashes a table has a higher risk of being
corrupted. Tables should at least be checked with isamchk *.ISM after
a crash.
mysqladmin reload before one tries to
check/repair tables with isamchk. (isamchk -d table_name is
always allowed).
The --skip-locking is default when compiling with MIT threads.
Actually using no VARCHAR or BLOB types results in a fixed row
size. Otherwise CHAR and VARCHAR are the same. Se the below
for a more exact definition.
You can check the format used for a table with isamchk -d.
mysql has three different table formats:
isamchk -r should be run now and then to
reorganize the table. This is to get a better layout. Use isamchk -ei
table_name for some statistics.
isamchk
-ed. All links may be removed with isamchk -r.
mysql_query() returns success, mysql_store_result() sometimes returns NULL.It means one of the following:
INSERT
or UPDATE or DELETE).
You can always check if the statement should given a result by checking that
mysql_num_columns(&MYSQL_CONNECTION) isn't 0. If this is 0
the last query was a statement that doesn't return values
INSERT, DELETE....
You have got an error if mysql_error(&MYSQL_CONNECTION) isn't empty!
mysql_affected_rows(MYSQL_RESULT) returns the number of affected
rows when doing an INSERT, UPDATE or DELETE.
mysql_insert_id(MYSQL_RESULT) returns the given ID when inserting
a row into a table with a AUTO_INCREMENT key.
Some querries return additional info what happened, for example
LOAD DATA INFILE.... This result is returned in
mysql_info(&mysql_RESULT). This result is a null pointer if there is
no additional information.
If you insert a record which have a AUTO_INCREMENT key then you
can get the given id with mysql_insert_id(last_result).
You can check if an auto_increment key is used by the following code:
if (mysql_error(MYSQL_RESULT)[0] == 0 &&
mysql_num_fields(MYSQL_RESULT) == 0 &&
mysql_insert_id(MYSQL_RESULT) != 0)
used_id = mysql_insert_id(MYSQL_RESULT);
mysql_use_result() do?
This says to mysql to use the result directly from the server (no
local store like in mSQL), which is somewhat faster and doesn't use
memory to buffer the hole result.
This shouldn't be used if there is much processing for each row at the
client side, or if the output is sent to a screen on which the user may
do a ^S (stop scroll), because it ties up the server. One can't use
mysql_data_seek when using mysql_use_result.
STRINGS
A string may have ' or " around it.
\ is a escape character. The following escape characters are recognized:
\0
\n
\t
\r
\b
\'
' character.
\"
" character.
\\
\ character.
\%
% character. This is used in wild-card strings to search after
%.
\_
_ character. This is used in wild-card strings to search after
_.
A ' inside a string started with ' may be written as ". A " inside a string started with " may be written as "".
mysql> select 'hello', "'hello'", '""hello""', "'h"e"l"l"o"', "hel""lo"; 1 rows in set (0.00 sec) +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ mysql> select 'hello', "hello", '""hello""', "'ello", 'e"l"lo', '\'hello'; 1 rows in set (0.00 sec) +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ mysql> select "This\nIs\nFour\nlines"; 1 rows in set (0.00 sec) +--------------------+ | This Is Four lines | +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a blob the following characters must be escaped with a backslash:
NULL
\
'
"
NUMBERS
Integers are just a sequence of digits.
Floats use . as a decimal separator.
Examples of valid numbers are: 1221, 294.42,
-32032.6809e+10.
CREATE TABLE table_name ( create_definition,... )
create_definition:
column_name type [NOT NULL] [DEFAULT default_value] [ PRIMARY KEY ]
or column_name type [NULL] [DEFAULT default_value] [ PRIMARY KEY ]
or PRIMARY KEY ( key_column_name,... )
or KEY [key_name] KEY( key_column_name,...)
or INDEX [key_name] ( key_column_name,...)
or UNIQUE [key_name] ( key_column_name,...)
or FOREIGN KEY key_name ( key_column_name,...) REFERENCES table_name [ ON DELETE { RESTRICT | CASCADE | SET NULL }]
key_column_namne:
column_namn [ (length) ]
TINYINT [(length)] [UNSIGNED] [ZEROFILL]
SMALLINT [(length)] [UNSIGNED] [ZEROFILL]
MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL]
INT [(length)] [UNSIGNED] [ZEROFILL]
INTEGER [(length)] [UNSIGNED] [ZEROFILL]
BIGINT [(length)] [UNSIGNED] [ZEROFILL]
REAL [(length,decimals)]
FLOAT [(length,decimals)]
DOUBLE [(length,decimals)]
DECIMAL [(length,decimals)]
CHAR(NUM)
VARCHAR(NUM)
BLOB
TINYBLOB
MEDIUMBLOB
LONGBLOB
TIMESTAMP(LENGTH)
INT(5) ZEROFILL a value of 5 is retrieved as 00005.
column_namn(length) syntax one can specify a key with is only
a part of a string column. This can make the index file much smaller.
isamchk utility to
reorganize tables.
VARCHAR
columns with a length of 1 or 2 are changed to CHAR. When using one
VARCHAR columns all CHAR columns longer than 2 are changed to
VARCHAR's.
INSERT/UPDATE all strings (CHAR and VARCHAR)
are silently chopped/padded to the maximal length given by CREATE. All end
spaces are also automatically removed. For example VARCHAR(10) means that the
column can contain strings with a length up to 10 characters.
REGEXP and RLIKE) uses
ISO8859-1 (Latin1) when deciding the type of a character.
ALTER [IGNORE] TABLE table_name alter_specification [, alter_specification ...]
alter_specification:
ADD [COLUMN] create_definition
or CHANGE [COLUMN] old_column_name create_definition
or ALTER [COLUMN] column_name { SET default | DROP DEFAULT }
or DROP [COLUMN] column_name
or DROP PRIMARY KEY
or DROP INDEX key_name
DROP FOREIGN KEY key_name
CHANGE column_name, DROP column_name and DROP
INDEX is mysql extensions to ANSI SQL92.
COLUMN is pure noiseword and can be omitted.
ADD and CHANGE takes the same create_definition as
CREATE TABLE. See section CREATE TABLE syntax..
ALTER COLUMN sets a new default value or removes the old
default value for a column.
DROP INDEX drops and index. This is an mysql extension.
DROP FOREIGN KEY is for future compatibility only. It doesn't do
anything.
DROP PRIMARY KEY drops the first UNIQUE key in table.
CHANGE tries to convert data to the new format as good as possibly.
mysql_info(&MYSQL_RESULT) one can retrive how many records
was copied and how many records was deleted because of multiple keys.
ALTER TABLE one have to have select, insert, delete,
update, create and drop privileges on the table.
DROP TABLE table_name [, table_name....]
Destroys one or more tables. The table is removed so take it easy with this command!
DELETE FROM table_name WHERE where_definition
Returns records affected.
If one does a delete without a where clause then the table is truncated
(This is much faster). In this case it returns records affected as zero.
This will work even if the datafiles doesn't exists, because it uses
only information from the table definition file, table_name.frm.
where_definition: where_expr or where_expr [ AND | OR ] where_expr where_expr: column_name [> | >= | = | <> | <= | < ] column_name_or_constant or column_name LIKE column_name_or_constant or column_name IS NULL or column_name IS NOT NULL or ( where_definition )
SELECT [DISTINCT | ALL] select_expression,... [ FROM tables... [WHERE
where_definition ] [GROUP BY column,...] [ ORDER BY column [ASC | DESC] ,..]
HAVING full_where_definition [LIMIT [offset,] rows] [PROCEDURE procedure_name]] [INTO OUTFILE 'file_name' ...]
+
-
*
/
NULL.
|
&
-
(,)
NOT
!
OR
AND
=
<>
<=
<
>=
>
expr LIKE expr
expr NOT LIKE expr
expr REGEXP expr
expr NOT REGEXP expr
ABS()
FLOOR()
CEILING()
ROUND()
EXP()
LOG()
LOG10()
POW()
SQRT()
RAND([integer_expr])
SIGN()
BIT_COUNT()
MOD()
%
MIN()
MAX()
CONCAT()
DATABASE()
LENGTH()
STRCMP()
LOCATE(A,B)
LOCATE(A,B,C)
LEFT(str,length)
RIGHT(str,right)
LTRIM(str)
RTRIM(str)
SUBSTRING(A,B,C)
REPLACE(A,B,C)
INSERT(org,start,length,new)
LCASE(A)
UCASE(A)
PASSWORD()
INTERVAL(A,a,b,c,d)
ELT(N,a,b,c,d,...)
BETWEEN(A,B,C)
PERIOD_ADD(P:N)
PERIOD_DIFF(A,B)
TO_DAYS()
FROM_DAYS()
WEEKDAY()
CURDATE()
NOW()
ISNULL(A)
IFNULL(A,B)
IF(A,B,C)
USER()
FORMAT(nr,NUM)
Functions for group by clause:
count(expr)avg(expr)min(expr)max(expr)sum(expr)
Sum of expr.
min() and max() may take string arguments. count(*) is
optimized to return directly if no other column is used.
These can't be used in expression, even if argument may be an expression.
Eg: SUM(value/10) is allowed but SUM(value)/10 is not (yet!).
Strings are automatically converted to numbers and numbers to strings when needed (ala perl). To get the operators '=, <>, <= ,<, >=,>' to work like in the WHERE statement, the left side decides if the test is done by numbers or by strings All string compares are done case-independent by ISO8859-1.
Examples:
"a" < "b"
"a" < 0
0 < "a"
aa < 5
SELECT user,MAX(salary) FROM users GROUP BY users HAVING max(salary)>10
Change it to:
SELECT user,MAX(salary) AS sum FROM users GROUP BY users having sum > 10
INSERT INTO table [ (column_name,...) ] VALUES (expression,...) or INSERT INTO table [ (column_name,...) ] SELECT ....
An expression make use any previous column in column_name list (or table if no column name list is given).
The following holds for a multi-row INSERT statement:
ORDER BY clause.
INSERT statement cannot appear in the FROM
clause of the query.
mysql_info().
@result{Records: 100 Duplicates: 0 Warnings: 0}
Duplicates are number of rows which couldn't be written because some
key would be duplicated. Warnings are columns which was set to NULL,
but has been declared NOT NULL. These will be set to their default value.
load data infile 'interval.tab'
[replace | ignore]
into table interval
[fields [terminated by ',' [optionally] enclosed by '"'
escaped by '\\' ]] [lines terminated by '\n']
[(field list)]
This is used to read rows from a textfile.
To write data to a textfile, use the
SELECT ... INTO OUTFILE 'interval.tab' fields terminated by ','
enclosed by '"' escaped by '\\' lines terminated by '\n'
syntax.
Normally you don't have to specify any of the textfile type options. The
default is a compact textfile with columns sepearated with tab
characters and all rows ends with a newline. Tabs, newlines and \
inside fields are prefixed with a \. NULL's are read and written as
\N.
fields terminated by has default value of \t.
fields [optionally] enclosed by has default value of "
fields escaped by has default value of '\\'.
lines terminated by has default value of '\n'.
fields terminated by and lines terminated by may be more
than 1 character.
If lines terminated by is empty and fields terminated by is
not empty then lines are also terminated by fields terminated by.
If fields terminated by and fields enclosed by both are empty
strings (") then this gives a fixed row size. With a fixed
row size NULL values are outputed as a blank string.
If you specify optionally in inclosed by and you don't use the
the fixed row size, then only strings are enclosed in enclosed by.
by the SELECT ... INTO statement.
When using LOAD duplicated enclosed by are removed inside
an enclosed by.
If escaped by is not empty then the following characters will
be prefixed with the escape character:
escaped by, ASCII 0, and the first character in any of
fields terminated by, fields enclosed by and
lines terminated by.
If fields enclosed by is not empty then NULL is read as a
NULL value. If fields escaped is not empty then \N
is also read as a NULL value.
If REPLACE is used the new row will replace all rows
which has a same unique key. If IGNORE is used all the row
will be skipped if there already exist a record with a identical
unique key. If no of the above options is used an error will be issued
and the rest of the textfile will be ignored if one gets a duplicate
key error.
Some possible cases that's not supported by LOAD DATA:
fields terminated by and fields enclosed by both are empty) and BLOB fields.
fields escaped by is empty and the data contains lines terminated by or fields enclosed by followed by fields terminated by.
All rows are read into table. If a row has too few fields the rest fields are set to default values.
For security reasons the textfile must either reside in the database directory or be readable by all.
For more info about the escaped syntax See section Literals. That is how are strings and numbers written?.
When the LOAD DATA query is done one can get the following info
string with the C API function mysql_info().
@result{Records: 1 Deleted: 0 Skipped: 0 Warnings: 0}
Warnings is incremented for each column which can't be stored without loss of precision, for each column which didn't get a value from the read text line (happens if line is too short) and for each line which has more data than can fit into the given columns.
UPDATE table SET column=expression,... WHERE where_definition
All updates are done from left to right. IF one accesses a column in the expression it uses the current value.
SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM database] [LIKE wild] or SHOW COLUMNS FROM table [FROM database] [LIKE wild]
Gives information about databases, tables or columns. The wild is a LIKE
string. FIELDS may be used as an alias for COLUMNS.
(DESCRIBE | DESC) table [column]
Gives information about columns. This command is for Oracle compability. See section SHOW syntax. Get information about names of columns. Column may be a column name or a string. Strings may contain wildcards.
CREATE [UNIQUE] INDEX index_name ON table_name ( column_name,... )
In mysql this only checks that the index already created with CREATE TABLE. You can create new index with ALTER TABLE. See section ALTER TABLE syntax
DROP INDEX index_name
This always succeeds. You can drop an index with ALTER TABLE. See section ALTER TABLE syntax
mysql picky about reserved words?
It seems that I can't create a table with column names timestamp or
group.
Functions doesn't clash with table or column names. (For example ABS
is an allowed column name). The only restriction is that space is not
allowed between a function name and the '(' when using functions.
The following are reserved words in mysql. Most of them (for example)
group, are forbidden by ANSI SQL as column and/or and a few are because
mysql needs them and (currently) is using a Yacc parser:
add, alter, all, and, as, asc,
auto_increment, bigint, blob,by, cascade,
char, change,
columns create, data, databases, decimal,
default, delete, desc, describe, distinct,
double,
drop, fields, float, float4, float8,
foreign, from, group,having, ignore,
in, index, insert, int, int1, int2,
int3, int4, int8, integer,
into, is, key, keys, like, limit,
longblob, mediumblob, mediumint, not,
null, on, or, order, primary,
procedure, real, references, regexp,
replace, restrict, rlike, select, set,
show,
smallint, straight_join, table, tables,
timestamp,
tinyblob, tinyint, unique, unsigned,
update, values, varchar, where, zerofill.
mysql
mysqladmin
mysqld
mysqldump
mysqlshow
safe_mysqld
isamchk
msql2mysql
replace
Move the offending part of the WHERE to the HAVING clause. Add
alias for expressions..
Example: (comment is a BLOB)
SELECT user,comment,salary/10 FROM users WHERE user = "monty" AND
comment LIKE "%word% AND salary/10 > 1
->
SELECT user,comment,salary/10 as new_salary FROM users WHERE user = "monty"
HAVING comment LIKE "%word% AND new_salary > 1
or make the BLOB into a fixed length object with the SUBSTRING or
similar functions.
If mysql crashed (f ex if the computer is turned off) when all data is not written to disk the tables may be crashed. To check a table use
isamchk table_name
isamchk -e table_name
isamchk -ei table_name
Always backup a table before trying to repair it.
To repair a table(REMEMBER TO BACKUP A TABLE BEFORE TRYING TO REPAIR IT!):
isamchk -r table_name
isamchk -rq table_name
To get a description/statistics from a table us the forms below. Sometimes we will explain some of the information in more detail.
isamchk -d table_name
ISAM file: company.ISM
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
Record format: Fixed length
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
For explanations se below.
isamchk -d -v table_name
ISAM file: company.ISM
Isam-version: 2
Creation time: 1996-08-28 11:44:22
Recover time: 1997-01-12 18:35:29
Data records: 1403698 Deleted blocks: 0
Datafile: Parts: 1403698 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 3
Max datafile length: 3791650815 Max keyfile length: 4294967294
Recordlength: 226
Record format: Fixed length
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 8 unique double 15845376 1024 1
2 15 10 multip. text packed stripped 25062400 1024 2
3 219 8 multip. double 40907776 1024 73
4 63 10 multip. text packed stripped 48097280 1024 5
5 167 2 multip. unsigned short 55200768 1024 4840
6 177 4 multip. unsigned long 65145856 1024 1346
7 155 4 multip. text 75090944 1024 4995
8 138 4 multip. unsigned long 85036032 1024 87
9 177 4 multip. unsigned long 96481280 1024 178
193 1 text
ISAM file
Isam-version
Creation time
Recover time
Data records
Deleted blocks
Datafile: Parts
Data
records.
Deleted data
Datafilepointer
Keyfile pointer
Max datafile length
.ISD) get.
Max keyfile length
.ISM) get.
Recordlength
Record format
Fixed
length.
table description
Key
Start
Len
Index
unique or multip.. Can one value exist multiple times in
this index.
Type
C data-type
with a optional packed or stripped.
Root
Blocksize
Rec/key
isamchk -a. If this is not updated at all a default
value of 30 is given.
isamchk -eis table_name
Checking ISAM file: company.ISM Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 17% Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Recordblocks: 1403698 Deleteblocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1626.51, System time 232.36 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 627, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary contexts switches 639, Involuntary contexts switches 28966
Keyblocks used
Packed
CHAR/VARCHAR/DECIMAL keys. For long string like
names this can reduce the spaced used significantly. In the above example
the 4 key is 10 characters long and gets a 60% reduction in space.
Max levels
Records
M.recordlength
Packed
Recordspace used
Empty space
Blocks/Record
isamchk. Se
See section How do you check/repair/reconstruct an mysql table?.
Recordblocks
Deleteblocks
Recorddata
Deleted data
Lost space
Linkdata
isamchk -eiv table_name
Checking ISAM file: company.ISM Data records: 1403698 Deleted blocks: 0 - check file-size - check delete-chain index 1: index 2: index 3: index 4: index 5: index 6: index 7: index 8: index 9: No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 - check data record references index: 3 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 - check data record references index: 5 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 6 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 7 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 8 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 9 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 9% Packed: 17% - check records and index references [LOTS OF ROW NUMBERS DELETED] Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Recordblocks: 1403698 Deleteblocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 Voluntary contexts switches 10604, Involuntary contexts switches 122798
Here is the data file sizes of the table used above.
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.ISD -rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.ISM
The privilege tables are read into mysqld with mysqladmin
reload. If the privilege tables are empty (or non-existent) full access
is granted to everyone. Full access to everyone is also granted if the
server is started with --skip-grant-tables.
% and _.
localhost, a hostname, ip or SQL expression.
A empty host means any host.
db table. This means that a super users only needs to be in the
user table with all privilege-flags set to Y.
Everything granted in the user database is valid for every database what does not have a separate db entry.
The host table is mainly to maintain a list of "secure" servers. At
TCX hosts contain a list of all machines on local network. These are
granted all privileges. Technically the user grant is calculated by:
". Under
each host sort user by the same criterias.
When matching, use the first found match.
create_priv and drop_priv are both for create and drop of tables and databases. Because a user with a drop grant can delete any table in this is the same thing as a drop grant for the database.
Create_priv and drop_priv is on tables and databases, the others are for rows.
You need also to understand how mysql searches for a match in the user table.
Mysql uses the first non-regexp match to decide which entry to use.
First host is matched, after that user. " in the host or user
field is the same as '%'.
This means that mysql first sorts all entries by host by putting host
without wildcards first, after this host with wildcards and entries with
host = ". Under each host sort user in the same way.
When matching, mysql uses the first match found.
This means that if the user table has, say:
HOST
%
%
localhost
localhost
Then the search order will be:
So jeffrey attempting to connect on localhost will be matched by the
localhost/any line, not by the any/jeffrey line.
So if you have access problems, print out the user table, sort it by hand, and see where the match is being made.
To add priviliges to the mysql database:
This assumes the current user has insert right for the db table and reload privileges. The server (mysqld) has to be running.If it is not start it with 'safe_mysqld --log &'.
> mysql mysql
insert into user values ('%','monty',password('something'),'Y','Y','Y','Y','Y','Y','Y','Y','Y') ;
insert into user (host,user,password) values('localhost','dummy',") ;
insert into user values ('%','admin',",'N','N','N','N','N','N','Y','N','Y') ;
quit
> mysqladmin reload
This makes three new users:
mysql.
mysqladmin reload
and mysqladmin processlist. May be granted individual database
privileges through table db.
db.
The default privileges (set in `scripts/mysql_install_db') is that
root can do anything. Any user can do anything with any database whose
name is 'test' or start with 'test_*'. A normal user can't use
mysqladmin shutdown or mysqladmin processlist. See the
script (`scripts/mysql_install_db') for a example on how to add
other users.
The privilege tables are read into mysqld with 'mysqladmin reload'. If the privilege tables are empty (or non-existent) full access are granted to everyone.
A common mistake is to try something like:
INSERT INTO user VALUES ('%','jeffrey','bLa81m0','Y','Y','Y','N','N','N','N','N', 'N');
Then (of course) a mysqladmin reload to make the authentication
change take effect, then trying to connect to the server:
$ ./mysql -h sqlserver -u jeffrey -p bLa81m0 test
Access denied
Try with this instead:
INSERT INTO user VALUES
('%','jeffrey',password('bLa81m0'),'Y','Y','Y','N','N','N','N','N','N');
And like before mysqladmin reload to make the authentication change
take effect.
Now things should work.
mysql without root access?
Yes. You just have to make some user to be able to create databases. The
easiest yat to do this is to change the word root to your user
name in the script `scripts/mysql_install_db'. And then run this
script.
Se the file PORTING in the distribution.
mysql ODBC support?
mysql ODBC is a 32 bit ODBC (2.50) for Windows95 and NT. We hope
somebody will port it to Windows 3.x.
mysql ODBC?We have only tested ODBC with Admndemo, some C programs, Msquery and Excel.
To give some light about any problem we would like to have the log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MYODBC log.
To get a MYODBC log, please put this MYSQL_DEBUG=d:t:O,filename in
your AUTOEXEC.BAT and restart.
The log will be written to file `filename'.
This document was generated on 5 March 1997 using the texi2html translator version 1.51.