Последнее изменение файла: 2001.05.21
Скопировано с www.bog.pp.ru: 2003.03.08

Bog BOS: MySQL: установка, настройка, описание

SQL СУБД (реляционная) без излишеств (правда, в последней версии появились транзакции с помощью Berkley DB и INNOBASE), зато быстрая (для поиска и добавления, если предстоят частые изменения, то лучше поискать другую СУБД). Стандарты: entry level SQL92, ODBC levels 0-2.

Лицензия - GPL/LGPL (но в случае извлечения прибыли от MySQL фирма - MySQL AB, Швеция - мягко намекает на оплату поддержки). Для хостинга лицензия не нужна, но клиенты должны иметь возможность убедиться, что все установлено правильно (предлагается давать доступ на чтение к установленным исходникам).

Написана на C и C++. Базовая платформа: Solaris 2.7-2.8, SuSE Linux 7.1 (ядро 2.4, ReiserFS), но работает также в AIX, BSDI, DEC Unix, FreeBSD, HP-UX, Linux 2.0, Mac OS X, NetBSD, OpenBSD, OS/2, SGI Irix, SunOS, SCO OpenServer, SCO UnixWare, Tru64, Win9x, NT, Win2000.

Многопотоковая. Первоначально мимикрировала под mSQL. API для C, C++, Java, Eiffel, Perl, PHP, Python, Tcl. ODBC. Парольная защита (пароли шифруются перед пересылке, это, однако, не увеличивает безопасность).

Таблицы в виде B-tree со сжатием индекса. До 32 индексов на таблицу. До 16 колонок на индекс. Длина индекса до 500 байт. Таблицы в памяти. Записи переменной длины. Есть примеры использования MySQL с 60000 таблиц и 5 миллиардами строк. Отсутствует memory leak (проверено Purify). Поддержка koi8-r и cp1251 (сортировка, сравнение и т.д.). Клиенты могут соединяться по TCP/IP (можно использовать только, если никто не подслушивает) или Unix socket. Можно встраивать в свои программы.

Стабильность подсистем: ISAM - стабильная, MyISAM - gamma, C API - стабильная (буфер до 16МБ), mysql(,admin,show,dump,import) - стабильные, Basic SQL - стабильная, оптимизатор - стабильная, блокировка (одновременный доступ нескольких процессов, не клиентов) - gamma (проблемы в Linux, рекомендуется --skip-locking), нити в Linux - рекомендуется --skip-locking и использовать не более 1000 одновременных соединений, DBD - стабильная, MyODBC - gamma, репликация - бета/gamma, BDB - бета (транзакции), автоматическое восстановление MyISAM - бета, слияние таблиц - бета/gamma, INNODB - альфа (транзакции с блокировкой на уровне строк), полнотекстовый поиск - бета.

Установка и настройка (Linux)
Опции ./configure
mysql.server (запуск MySQL при загрузке копьютера)
safe_mysqld (надстройка над mysqld)
mysqld (сервер БД)
Разбор параметров и конфигурационных файлов
Типы таблиц (методы доступа)
Права доступа и привилегии Базовый синтаксис
Типы данных (в колонках)
Операторы SQL
Функции для SELECT и WHERE
Отличия версий
Ссылки

• иметь gcc лучше, чем 2.8.1 (egcc 1.0.2), рекомендуется 2.95.2
• создать директорию для сборки, распаковать в нее mysql-3.23.32.tar.gz/mysql-3.23.37.tar.gz (взять на http://download.sourceforge.net/mirrors/mysql/)
• для версии ранее 3.23.34 распаковать туда db-3.2.3h.tar.gz (это специальная версия bdb для MySQL)
• создать группу mysql
• создать пользователя mysql (в группе mysql) (зачем ему bash?)
• ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-unix-socket-path=путь --with-mysqld-user=mysql --disable-large-files --with-libwrap --without-debug --with-charset=cp1251 --with-extra-charsets=all --with-berkeley-db
• make (70MB/91MB)
• если upgrade, то остановить mysql, сохранить базы данных и my.cfg (не забыть потом удалить!)
• make install (как root)(16 MB, из них 5МБ - тест)
  • /usr/local/mysql/include/mysql
  • /usr/local/mysql/info
  • /usr/local/mysql/man
  • /usr/local/mysql/lib/mysql (libdbug, libheap, libmerge, libmyisam, libmyisammrg, libmysqlclient, libmystrings, libmysys, libnisam); эту директорию указывать для libtool, либо занести в /etc/ld.so.conf
  • /usr/local/mysql/bin (comp_err, isamchk, isamlog, my_print_defaults, myisamchk, myisamlog, myisampack, mysql, mysqladmin, mysqlbinlog, mysqlshow, mysqldump, mysqlimport, mysqltest, pack_isam, perror, replace, resolve_stack_dump, resolveip, скрипты )
  • /usr/local/mysql/share/mysql (сообщения об ошибках - koi8-r, таблицы кодировок - cp1251, koi8-r; mi_test_all; mi_test_all.res)
  • /usr/local/mysql/libexec (mysqld)
  • /usr/local/mysql/sql-bench
  • /usr/local/mysql/mysql-test
  • /usr/local/mysql/data (базы данных, конфигурация, журналы, pid-файл)
• при первой установке: scripts/mysql_install_db (как root - создание таблиц с правами доступа, дает все права пользователю root без пароля и позволяет делать все с базами test и test_*, кроме раздачи привилегий)
• chown -R root:mysql /usr/local/mysql (как root)
• chown -R mysql /usr/local/mysql/data (и отдельную директорию для mysql.sockets с правами чтения для всех; mysql не нужны права на запись для my.cnf)
• support-files/mysql.server в /etc/rc.d/init.d для автоматического запуска и дать ему права на исполнение и сделать линк K00mysql из rc0.d и rc6.d на него, S99mysql из rc2.d, rc3.d и rc5.d на него
• скопировать my-medium.cnf в /usr/local/mysql/data/my.cnf и слегка отредактировать [mysqld]
  • socket=имя-файла под Unix-socket (и в раздел [client] тоже)
  • skip-locking (не блокировать доступ к данным от ДРУГИХ процессов)
  • log-bin #журнал изменений для репликации
  • log-slow-queries
  • log-update #журнал изменений
  • #secure - говорит, что нет такой опции
  • skip-networking #если не нужен доступ по TCP/IP (а еще лучше использовать ssh + port forward)
  • safe-show-database
  • skip-show-database
  • server-id=1 # что это?
  • куча set-variable из исходного файла
  • set-variable = max_connections=500
• тестовый запуск: /usr/local/mysql/bin/safe_mysqld --user=mysql (как root)(или сразу /etc/rc.d/rc3.d/S99mysql start)
• /usr/local/mysql/bin/mysqladmin -u root -p password 'пароль' (при запросе пароля нажать Enter)
• /usr/local/mysql/bin/mysqladmin -u root -h localhost.localdomain -p password 'пароль' (надо ли?)
• установка интерфейса с Perl
  • взять на www.mysql.com модули Data-Dumper, DBI и Msql-Mysql-modules
  • каждый распаковать в отдельную директорию (Msql-Mysql-modules последним)
  • зайти в нее
  • perl Makefile.PL (опционально хочет RPC::PlServer, RPC::PlClient, Storable, Net::Daemon).
  • make
  • make test (mysqld должен работать)
  • make install (как root)
• тестирование
  • зайти в sql-bench
  • ./run-all-tests --user=test (нужны права для записи в директорию output, час времени и 200 МБ на диске)(connect/disconnect временами грохает mysqld!)
  • можно удалить sql-bench и mysql-test
• настроить права доступа (как минимум, убрать анонимный доступ)

• --prefix=куда-устанавливать (множество мелочных опций по установке)
• --enable-maintainer-mode [no]
• --enable-shared (делать разделяемые библиотеки)[yes]
• --enable-static (10% быстрее)[yes]
• --with-mit-threads (для linux 2.2 не надо)
• --with-pthread (для linx 2.2 не надо)
• --with-named-thread-libs=где
• --with-named-curses-libs=где
• --with-named-z-libs=где
• --enable-thread-safe-client (если клиентская программа использует потоки)
• --enable-assembler
• --with-raid
• --with-unix-socket-path=куда-класть-unix-socket
• --with-tcp-port=порт [3306]
• --with-mysqld-user=имя-пользователя-для-mysqld
• --disable-large-files
• --with-libwrap
• --without-debug (15% быстрее)
• --without-server
• --without-docs
• --without-bench
• --without-readline (использовать системный readline вместо встроенного)
• --with-charset=кодировка-по-умолчанию (cp1251, koi8_ru, latin1, ...)
• --with-extra-charsets=список-дополнительных-кодировок (включая - none, complex, all)
• --with-berkeley-db
• --with-innodb
• --with-gemini (Gemini DB)

Вместо ключей запуска можно использовать конфигурационные файлы.

• --ansi (бОльшая совместимость с ANSI SQL)
• --basedir=путь
• --bdb-lock-detect=[DEFAULT | OLDEST | RANDOM | YOUNGEST | число-секунд]
• --bdb-logdir=директория
• --bdb-no-sync
• --bdb-no-recover
• --bdb-shared-data (запускать Berkeley DB в режиме разделения с другими процессами)
• --bdb-tmpdir=директория
• --bind-address=IP-адрес (для хостов с несколькими адресами)
• --chroot=путь (умеет?!)
• --core-file
• --datadir=путь
• --default-character-set=кодировка (сменил кодировку - перестрой индексы)
• --default-table-type=метод-хранения [MyISAM]
• --defaults-extra-file=дополнительно-к-my.cnf
• --defaults-file=имя-файла-параметров (вместо my.cnf)
• --enable-locking (внешняя блокировка - если нужна блокировка нескольких серверов или myisamchk к одной базе данных; работает неустойчиво и медленно)
• --flush (сбрасывать буфера на диск после каждой SQL-команды
• --help (выдает список команд; настройки, выбранные при сборке с учетом текущих параметров и значения переменных)
• --init-file=имя-файла (при запуске читать команды из файла)
• --language (язык сообщений об ошибках)
• --log[=имя-файла] (записывать все соединения и команды в журнал)
• --log-bin[=имя-файла] (записывать журнал в новом двоичном формате для репликации)
• --log-bin-index=имя-файла (хранит имена последних бинарных журналов)
• --log-slow-queries[=имя-файла] (записывать все слишком медленные - занимающие более long_query_time секунд - команды в журнал)
• --log-update[=имя-файла] (записывать изменения в журнал)
• --log-long-format (дополнительную информацию в журнал изменений)
• --low-priority-updates (INSERT/DELETE/UPDATE будут иметь меньший приоритет, чем SELECT)
• --memlock (не свопировать mysqld)
• --myisam-recover[=опция...] (проверка корректности myisam таблиц и способ восстановления):
  • DEFAULT
  • BACKUP (делать резервную копию при исправлениях - .BAK)
  • FORCE (исправлять даже если это приведет к потере многих данных)
  • QUICK
• --new (использовать новые, т.е. недоотлаженные возможности)
• --no-defaults
• --port=TCP-порт
• -O имя=значение (установить переменную, список по --help)
• --safe-mode (уменьшить уровень оптимизации)
• --safe-show-database (не показывать имена БД пользователям, которые не имеют к ним никаких прав)(или нет такой опции? в списке ее нет, но не ругается)
• --secure (двойная проверка IP-адресов - есть ли такая опция? если ее указать, то не стартует. Или она теперь по умолчанию? Если нет, то именами хостов вообще нельзя пользоваться!)
• --skip-bdb
• --skip-delay-key-write (отключить буферизацию записи ключей)
• --skip-grant-tables (отключить проверку прав доступа)
• --skip-locking (не использовать внешнюю блокировку; safe_mysqld использует этот ключ; ключ применяется, если не нужна блокировка нескольких серверов или myisamchk к одной базе данных; все равно внешняя блокировка работает неустойчиво и медленно; команда LOCK работает даже с этим ключом)
• --skip-name-resolve (в таблицах прав доступа д.б. только IP)
• --skip-networking (не слушать TCP/IP, только через unix socket)
• --skip-new (не использовать новые, т.е. не доотлаженные возможности)
• --skip-show-database (не позволять команду SHOW DATABASE, если пользователь не имеет привилегии работы с процессами. В другом месте говорится, что вообще ничего не показывать - но это неправда)
• --skip-thread-priority (ускоряет ответ)
• --socket=путь
• --tmpdir=путь (вместо /tmp)
• --user=имя-пользователя (обязательно, если запускается из под root)

Действуют на mysql, mysqld, mysqladmin, mysqlimport, mysqldump, myisamchk, myisampack, mysql.server. Файл делится на секции. Секция начинается со строки, в которой указано имя секции в квадратных скобках. Имя секции соответствует имени программы на которую она действует. Любая длинная опция программы (начинающаяся с двух минусов, запустите программу с ключом --help) может быть использована здесь (минусы надо отбросить). Секция с именем client позволяет задать опции (например, пароль ;), общие для всех клиентских программ. Переменные устанавливаются строкой:
set-variable = имя=значение.

Вся информация о правах хранится в БД с именем mysql. Никто не должен иметь к ней доступа на чтение (см. про пароли).

Проверка права на подсоединение к серверу: mysql-клиент предъявляет имя пользователя, сервер определяет имя (или IP) хоста клиента (или localhost для обращения через unix-socket). По данной паре (адрес/имя) ищется строка в таблице user. Предварительно таблица сортируется по полям (Host/User) так, что наиболее специфичные строки оказываются первыми, наименее специфичные - последними. Если строка не найдена, то соединение отвергается. Если - найдена, то сверяется пароль.

Проверка прав при исполнении каждого запроса: таблица db сортируется по полям Host, Db и User, таблица host по полям Host и Db, таблицы tables_priv и columns_priv по полям Host, Db и User от наиболее специфичного к наименее. Для административных запросов и доступа к файлам проверяется только таблица user. Для прочих запросов в начале проверяется таблица user - а нет ли у данного пользователя прав доступа на "глобальном" уровне. Если есть - операция разрешается. Если нет, то проверяются права доступа к конкретной БД с конкретного хоста (по пересечению таблиц Db и Host с учетом шаблонов и пустых полей). Если их достаточно, то доступ дается. Если недостаточно, то к объединению "глобальных" прав и прав БД/хост добавляются права, извлеченные из таблиц tables_priv и columns_priv. Если и этого не хватает, то увы...

"Глобальные" привилегии и изменения пароля вступают в силу только при следующем соединении. Изменения в доступе к БД - при следующей команде use. Изменения в доступе к таблицам и колонкам - при следующем запросе.

Все команды, введенные в клиенте mysql (а в других?) попадают в .mysql_history (того unix-пользователя, который запускал mysql). А также в различные журналы сервера и в выдачу команды SHOW PROCESSLIST.

Изменение пароля: set password for имя=password('новый пароль').

GRANT тип-привилегии [(список-столбцов)] [, тип-привилегии [(список-столбцов)] ...] ON { имя-таблицы | * | *.* | имя-БД.* } TO имя-пользователя [ IDENTIFIED BY 'пароль' ] [, имя-пользователя [ IDENTIFIED BY 'пароль ] ... ] WITH GRANT OPTION

REVOKE тип-привилегии [(список-столбцов)] [, тип-привилегии [(список-столбцов)] ...] ON { имя-таблицы | * | *.* | имя-БД.* } FROM имя-пользователя [, имя-пользователя ... ]

Для столбцов можно задавать только INSERT, SELECT и UPDATE. Для таблиц - INSERT, SELECT, UPDATE, CREATE, DROP, DELETE, GRANT, INDEX, ALTER. Использование "*.*" означает задание глобальных привилегий. Использование "*" означает задание привилегий для текущей БД (если текущей БД нет, то глобальные).

SHOW GRANTS FOR имя@хост;

Если привилегии на уровне таблиц и колонок используются хотя бы для одного пользователя, то проверки делаются для всех запросов всех пользователей, что очень сильно замедляет работу.

Для упрощения жизни можно использовать утилиты mysqladmin и mysqlaccess, xmysqladmin, mysql_webadmin из директории Contrib.

Посмотрев на это безобразие, ORACLE может перестать волноваться ;) Зря они различают пользователей с одним именем, пришедших с разных хостов (наверное, когда-то mysql-имя совпадало с unix-именем и этой информации м.б. доверять :). Не говоря об отсутствии шифровки при передаче данных по сети и возможность подсовывать перехваченный зашифрованный пароль

Не храните пароли и тому подобную информацию в БД в открытом виде.

Константы

• строка (в апострофах или кавычках, обратная косая черта как escape-символ)
• целое число (64 бита)
• вещественное число
• шестнадцатеричное число: в численном контексте как целое (64-бит), в строковом контексте пара 16-ричных цифр преобрацуется в символ
• NULL (\N при экспорте/импорте)

Имена

• БД (имя файла до 64 символов, кроме '/' и точки, в некоторых случаях до 60 символов)
• таблица (имя файла до 64 символов, кроме '/' и '.')
• колонка (до 64 любых символов). Можно ссылаться:
  • col_name
  • tbl_name.col_name
  • db_name.tbl_name.col_name
• алиас (до 255 любых символов)

Если в имени есть спецсимволы или оно совпадает с зарегистрированным словом, то его надо заключать в апострофы. Нельзя использовать символы 0x00 или 0xFF (привет маленькому "я" в cp1251!), апостроф и кавычку. Имена БД и таблиц чувствительны к регистру в Unix и нечувствительны в MS Windows. Имена колонок нечувствительны везде. Алиасы на таблицы чувствительны везде, алиасы на колонки нечувствительны везде.

Переменные

Комментарии

• как в C (начиная с '/*' до '*/'); если после '/*' стоит восклицательный знак и номер версии, то содержимое комментария исполняется, если номер версии равен или больше указанного
• как в sh (начиная с '#' до конца строки)
• как в SQL :) (начиная с '-- ' до конца строки, не забудьте про пробел!)

M - ширина поля при отображении (максимально - 255). D - число знаков в дробной части (не более M-2 и 30).

• Числовые (если число занимает меньше M позиций, то оно дополняется слева пробелами или нулями для ZEROFILL. Если число занимает более M колонок, то выводятся все цифры. Если используется ZEROFILL для целых, то автоматически добавляется UNSIGNED). Все вычисления проводятся с точностью 63 бита или переводом в DOUBLE (кроме нескольких функций).
  • TINYINT[(M)] [UNSIGNED[ [ZEROFILL] (1 байт)
  • SMALLINT[(M)] [UNSIGNED[ [ZEROFILL] (2 байта)
  • MEDIUMINT[(M)] [UNSIGNED[ [ZEROFILL] (3 байта)
  • INT[(M)] [UNSIGNED[ [ZEROFILL] (4 байта)
  • INTEGER[(M)] [UNSIGNED] [ZEROFILL] (4 байта)
  • BIGINT[(M)] [UNSIGNED] [ZEROFILL] (8 байт)
  • FLOAT[(M,D)] [ZEROFILL] (4 байта)
  • DOUBLE [PRECISION][(M,D)] [ZEROFILL]
  • REAL[(M,D)] [ZEROFILL] (синоним DOUBLE)
  • DECIMAL[(M[,D])] [ZEROFILL] (неупакованное десятичное вещественное число)
  • DEC - синоним DECIMAL
  • NUMERIC[(M[,D])] [ZEROFILL] (синоним DECIMAL)
• Дата и время (проверка значений не очень тщательная, например, дата 1999-11-31 будет воспринята нормально; допусимы номер дня, равный нулю, и номера месяца И дня равные нулю; неверное значение преобразуется в "нулевое" - 0000-00-00 и т.д.; заносить можно как строки с разделителями полей - любыми -, так и без разделителей или числа)
  • DATE (дата от '1000-01-01' до '9999-12-31'; 3 байта)
  • DATETIME (время от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'; 8 байт)
  • TIMESTAMP[(M)] (время от 19700101000000 до 2037 года; 4 байта; если при INSERT или UPDATE (должны быть реальные изменения) значение опущено (только первая колонка этого типа) или равно NULL или NOW(), то заносится текущее время; формат выдачи зависит от M (хранится всегда все; задавать надо тоже все):
    • 14 (по умолчанию) - YYYYMMDDHHMMSS
    • 12 - YYMMDDHHMMSS
    • 8 - YYYYMMDD
    • 6 - YYMMDD
  • TIME (время от -838:59:59 до 838:59:59; выводится как HH:MM:SS; 3 байта; можно вводить как 'D HH:MM:SS.fraction', хотя доли пока не хранятся)
  • YEAR[(2|_4_)] (от 1901 до 2155 или от 1970 до 2069; 1 байт)
• Строки (в MySQL CHAR всегда NATIONAL; надо учитывать, что максимальная длина строки таблицы и пакета протокола клиент/сервер - 16 МБ - и надо увеличить размер каких-то буферов как сервера, так и клиента; VARCHAR, BLOB и TEXT - строки переменной длины; BINARY - навязчивый атрибут, т.е. если операнд выражения - BINARY, то все выражение - BINARY; TEXT - это BLOB, который сортируется и сравнивается в нечувствительной к регистру манере; TEXT можно рассматривать как VARCHAR нужного размера, а BLOB можно рассматривать как VARCHAR BINARY нужного размера, но хвостовые пробелы не удаляются и не м.б. значения по умолчанию; для CHAR и VARCHAR можно индексировать префикс колонки; для BLOB и TEXT обязательно индексировать префикс фиксированной длины; для VARCHAR и TEXT можно создавать индекс специального вида FULLTEXT для индексирования всей колонки; ORDER BY и GROUP BY работают только с объектами фиксированной длины или первыми max_sort_length байтами)
  • [NATIONAL] CHAR(M) [BINARY] (фиксированной длины от 1 до 255; дополняется справа пробелами; при извлечении правые пробелы удаляются; сортируются и сравниваются нечувствительно к регистру относительно набора символов по умолчанию, если не указано BINARY; CHAR(0) занимает 1 бит и может принимать два значения - NULL и "")
  • NCHAR - синоним NATIONAL CHAR
  • CHARACTER - синоним CHAR
  • [NATIONAL] VARCHAR(M) [BINARY] (строка переменной длины от 1 до 255; правые пробелы удаляются при сохранении; сортируются и сравниваются нечувствительно к регистру относительно набора символов по умолчанию, если не указано BINARY)
  • CHARACTER VARYING - синоним VARCHAR
  • TINYBLOB (BLOB макс. длины 255)
  • TINYTEXT (TEXT макс. длины 255)
  • BLOB (BLOB макс. длины 65535)
  • TEXT (TEXT макс. длины 65535)
  • MEDIUMBLOB (BLOB макс. длины 2^24-1)
  • MEDIUMTEXT (TEXT макс. длины 2^24-1)
  • LONGBLOB (BLOB макс. длины 2^32-1, в действительности строка таблицы ограничена 16МБ)
  • LONGTEXT (TEXT макс. длины 2^32-1)
• ENUM('строка1','строка2',...) (может принимать значение из списка, NULL или "" - как признак ошибки при вставке; не более 65535 различных значений; 1 или 2 байта; если описан как NULL, то по умолчанию вставляется NULL; если описан как NOT NULL, то по умолчанию вставляется первая строка из списка; каждая строка имеет индекс, начиная с 1; индекс "" равен 0; индекс NULL равен NULL; в числовом контексте извлекается индекс, а не строка (как при вставке, так и при извлечении); сортировка производится по индексу; регистр букв не влияет при вставке, но извлекается в том виде, в каком колонка описывалась)
• SET('строка1','строка2',...) (может одновременно принимать от 0 до 64 значений из списка; элемент списка не может содержать запятую; 1, 2, 3, 4 или 8 байт;если значение SET извлекается/устанавливается в числовом контексте, то в числе каждому установленому биту соответствует элемент множества (первый элемент в описании множества соответствует младшему биту); сортируются как числа)

• операции над базами данных
  • CREATE DATABASE [IF NOT EXISTS] db_name
  • DROP DATABASE [IF EXISTS] db_name
• операции над таблицами
  • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя-таблицы [(описание-колонки,...)] [опции] [[IGNORE | REPLACE] SELECT ...]
    • описание-колонки
      • имя-колонки тип [NOT NULL | NULL] [DEFAULT значение-по-умолчанию] [AUTO_INCREMENT] [PRIMARY KEY] [описание-ссылки]
        NULL: по умолчанию
        AUTO_INCREMENT: для колонки типа целое число, при вставке NULL или 0 в такую колонку ищется максимальное значение колонки и увеличивается на единицу; отсчет начинается с 1; при удалении строки с наибольшим значением бывает всякое ;); такая колонка м.б. только одна и она д.б. проиндексирована; не надо записывать в такую колонку отрицательных чисел; получить наибольшее число можно с помощью оператора: SELECT * FROM имя-таблицы WHERE имя-колонки IS NULL
        DEFAULT: назначается системой (NULL, 0, пустая строка и т.д.), если Вы не установили явно
        SELECT позволяет создать новую таблицу на основе уже существующей
      • PRIMARY KEY (имя-колонки[(длина)],...)
        уникальный ключ; все ключевые колонки д.б. NOT NULL; только один на таблицу (присваивается имя PRIMARY)
      • {KEY | INDEX} [имя-индекса] (имя-колонки[(длина)],...)
      • UNIQUE [INDEX] [имя-индекса] (имя-колонки[(длина)],...)
      • FULLTEXT [INDEX] [имя-индекса] (имя-колонки[(длина)],...)
      • [CONSTRAINT символ] FOREIGN KEY имя-индекса (имя-колонки[(длина)],...) [описание-ссылки]
        не реализовано
      • CHECK (выражение)
        не реализовано
    • описание-ссылки ::= REFERENCES имя-таблицы [(имя-колонки[(длина)],...)] [MATCH FULL | MATCH PARTIAL] [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
      не реализовано
    • опции
      • TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MYISAM } (см, типы таблиц)
      • AUTO_INCREMENT = начальное-значение
      • AVG_ROW_LENGTH = число
      • CHECKSUM = {0 | 1}
      • COMMENT = "строка-до-60-символов"
      • MAX_ROWS = число
      • MIN_ROWS = число
      • PACK_KEYS = {0 | 1}
        чтение происходит быстрее, а изменение - медленнее
      • PASSWORD = "пароль"
        шифруется описание таблицы (в бесплатной версии не работает)
      • DELAY_KEY_WRITE = {0 | 1}
      • ROW_FORMAT= { default | dynamic | fixed | compressed }
        не реализовано
      • RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=число-расслоений RAID_CHUNKSIZE=числоKB
        требуется ключ --with-raid при сборке; пока реализован только тип STRIPED (1 и RAID0 - это синонимы для STRIPED); позволяет создавать таблицы с данными более 2GB (или распараллелить ввод/вывод, если разнести директории на разные диски)
      • UNION = (имя-таблицы,[имя-таблицы...])
        только для таблиц типа MERGE
  • ALTER [IGNORE] TABLE имя-таблицы как-менять [,как-менять ...]
    • ADD [COLUMN] описание-колонки [FIRST | AFTER имя-колонки ]
      без FIRST или AFTER колонка вставляется в конец
    • ADD [COLUMN] (описание-колонки, описание-колонки, ...)
    • ADD INDEX [имя-индекса] (имя-колонки[(длина)],...)
    • ADD PRIMARY KEY (имя-колонки[(длина)],...)
    • ADD UNIQUE [имя-индекса] (имя-колонки[(длина)],...)
    • ADD FULLTEXT [имя-индекса] (имя-колонки[(длина)],...)
    • ALTER [COLUMN] имя-колонки {SET DEFAULT константа | DROP DEFAULT}
    • CHANGE [COLUMN] имя-существующей-колонки описание-колонки
    • MODIFY [COLUMN] описание-колонки
    • DROP [COLUMN] имя-колонки
    • DROP PRIMARY KEY
    • DROP INDEX имя-индекса
    • RENAME [TO] новое-имя-таблицы
    • ORDER BY имя-колонки
    • опции таблицы
    делается временная копия таблицы, изменяется, затем старая таблица удаляется (соответственно, нужны привилегии: select, insert, delete, update, create и drop)
    IGNORE: если в преобразованной таблице образуются одинаковые значения UNIQUE индексов, то берется только одна строка (без IGNORE операция откатывается)
    нельзя сделать размер колонки короче, чем используется в каком-либо индексе
  • RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...]
    оператор выполняется как единое целое, что позволяет подменить таблицу пустой:

    CREATE TABLE new_table (...);
    RENAME TABLE old_table TO backup_table, new_table TO old_table;
    

  • DROP TABLE [IF EXISTS] имя-таблицы [, имя-таблицы,...] [RESTRICT | CASCADE]
    RESTRICT и CASCADE не реализованы
• обслуживание таблиц
  • OPTIMIZE TABLE имя-таблицы [, имя-таблицы,...]
    полезно после больших удалений или изменений таблиц с переменной длиной строки: сортировка индексов и дефрагментация
  • CHECK TABLE имя-таблицы [, имя-таблицы,...] {QUICK | FAST | MEDIUM | EXTEND | CHANGED}
  • BACKUP TABLE имя-таблицы [, имя-таблицы,...] TO 'имя-директории'
  • RESTORE TABLE имя-таблицы [, имя-таблицы,...] FROM 'имя-директории'
  • ANALYZE TABLE имя-таблицы [, имя-таблицы,...]
    пересчет распределения ключей
  • REPAIR TABLE имя-таблицы [, имя-таблицы,...] [QUICK] [EXTENDED]
• операции над строками
  • DELETE [LOW_PRIORITY] FROM имя-таблицы [WHERE выражение] [LIMIT макс-число-строк]
    место не освобождается - используйте OPTIMIZE
  • TRUNCATE TABLE имя-таблицы
    удаляет таблицу и тут же создает заново
  • SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]
       выражение [AS имя-алиаса],...
       [INTO {OUTFILE | DUMPFILE} 'имя-файла' опции-экспорта
       [FROM из-каких-таблиц
       [WHERE выражение]
       [GROUP BY {номер-колонки | имя-колонки | формула} [ASC | DESC], ...]
       [HAVING выражение]
       [ORDER BY {номер-колонки | имя-колонки | формула} [ASC | DESC], ...]
       [LIMIT [смещение-от-0,] макс-число-строк]
       [PROCEDURE имя-процедуры]
       [FOR UPDATE | LOCK IN SHARE MODE]]
    FROM - простейший случай синтаксиса - имя-таблицы, если таблиц несколько, то смотри синтаксис JOIN (в простейшем случае имена перечисляются через запятую); также можно задавать алиас таблицы с помощью слова AS
    HAVING применяется последним (в частности, после GROUP BY) непосредственно перед посылкой результата клиенту (т.е. без оптимизации!)
    DUMPFILE: в файл записывается одна строка безо всяких разделителей полей и строк и escape (позволяет, например, сбросить картинку из BLOB)
    OUTFILE и DUMPFILE: необходимо иметь привилегии file, если файл с указанным именем существовал, то он не затирается; права к файлу устанавливаются на чтение для всех
    FOR UPDATE: если тип таблицы позволяет блокировку страницы/строки (InnoDB, GEMINI), то задействованные строки блокируются на запись
  • синтаксис JOIN (только не надо вопросов ко мне, задавайте их этим фантазерам :)
    • таблица::= имя-таблицы [[AS] алиас] [USE INDEX (список-ключей)] [IGNORE INDEX (список-ключей)]
    • таблица, таблица
      это синоним INNER JOIN
    • таблица [CROSS] JOIN таблица
    • таблица INNER JOIN таблица {ON условное-выражение | USING (список-колонок)}
    • таблица STRAIGHT_JOIN таблица
    • таблица LEFT [OUTER] JOIN [ON условное-выражение | USING (список-колонок)]
    • таблица NATURAL [LEFT [OUTER]] JOIN таблица
    • таблица LEFT OUTER JOIN таблица ON условное-выражение
    • таблица RIGHT [OUTER] JOIN [ON условное-выражение | USING (список-колонок)]
    • таблица NATURAL [RIGHT [OUTER]] JOIN таблица
  • INSERT
  • LOAD FILE

Между именем функции и скобкой не должно быть пробела. Операндами могут быть константы, имена колонок и переменные.

• группировка функций с помощью круглых скобок
• арифметические операции: +, -, *, /
• битовые функции: |, &, <<, >>, ~, BIT_COUNT()
• логические функции: !, ||, && (возвращают 1, 0, или NULL)
• сравнение:
  • =, <>, !=, <=, <, >=, >
  • <=> (сравнение, позволяющее сравнивать NULL)
  • expr IS NULL, expr IS NOT NULL
  • expr BETWEEN min AND max
  • expr IN (значение1,...)
  • expr NOT IN (значение1,...)
  • ISNULL()
  • COALESCE(список) - возвращает первый ненулевой элемент списка
  • INTERVAL(N,N1,N2,N3,...) - возвращает номер интервала, т.е. 1, если N < N1; 2 если N < N2 и т.д.
• сравнение строк (если выражение чувствительно к регистру, то и сравнение будет чувствительным к регистру)
  • expr LIKE шаблон [ESCAPE 'escape-символ'] (соответствие простому регулярному выражению SQL; "%" - любое количество любых символов; "_" - ровно один символ; escape-символ по умолчанию - "\"; чтобы найти '\n' надо задать в шаблоне '\\n'; чтобы найти '\' - '\\\\')
  • expr NOT LIKE шаблон [ESCAPE 'escape-символ']
  • expr REGEXP шаблон (расширенное регулярное выражение)
  • expr RLIKE шаблон (расширенное регулярное выражение)
  • expr NOT REGEXP шаблон
  • expr NOT RLIKE шаблон
  • STRCMP(expr1,expr2) (арифметическое сравнение строк: -1 если первая строка меньше, 0 если строки равны, 1 если первая строка больше)
  • MATCH (колонка1,колонка2,...) AGAINST (expr) (возвращает меру релевантности - положительное вещественное число - между содержимым колонок и выражением; должен быть создан FULLTEXT индекс)
• преобразование типов
  • BINARY (преобразование строки в двоичную - т.е. чувствительную к регистру)
• условные выражения
  • IFNULL(expr1,expr2) (если expr1 есть NULL, то возвращает expr2, иначе expr1)
  • NULLIF(expr1,expr2) (если выражения равны, то возвращает NULL, иначе - expr1; expr1 может вычисляться дважды)
  • IF(expr1,expr2,expr3) (если expr1, то возвращает expr2, иначе expr3)
  • CASE значение1 WHEN [значение2] THEN результат1 [WHEN [значение3] THEN результат2 ...] [ELSE результатn] END (если значение1 равно значение2, то вернуть результат1, иначе сравнить значение1 и значение3 и т.д.; в противном случае результатn; если его нет, то NULL)
  • CASE WHEN [условие1] THEN результат1 [WHEN [условие2] THEN результат2 ...] [ELSE результатn] END
• математические функции (в случае ошибки возвращают NULL; подробнее см. библиотеку C)
  • унарный "-"
  • ABS(X), SIGN(X), MOD(N,M), FLOOR(X), CEILING(X), ROUND(X), ROUND(X,число-цифр-после-точки), EXP(X), LOG(X), LOG10(X), POWER(X,Y), SQRT(X), PI(), COS(радиан), SIN(радиан), TAN(радиан), ACOS(X), ASIN(X), ATAN(X), ATAN2(Y,X), COT(X), RAND(), RAND(seed), LEAST(X,Y,...), GREATEST(X,Y,...), DEGREES(радиан), RADIANS(градусов), TRUNCATE(X,число-цифр-после-точки)
• обработка строк (возвращают NULL, если длина результата больше max_allowed_packet; позиции нумеруются с 1)
  • ASCII(str) - ASCII-код первого символа строки
  • ORD(str) - ASCII-код с учетом мультибайтных символов
  • CONV(N,основание1,основание2) - преобразование числа из представления с основанием1 в представление с основанием2
  • BIN(N) - синоним CONV(N,10,2)
  • OCT(N) - синоним CONV(N,10,8)
  • HEX(N) - синоним CONV(N,10,16)
  • CHAR(целое,...) - возвращает строку, составленную из символов с соответствующими кодами
  • CONCAT(str1,str2,...)
  • CONCAT_WS(разделитель,str1,str2,...) - добавляет разделитель между сливаемыми строками
  • LENGTH(str), OCTET_LENGTH(str)
  • CHAR_LENGTH(str), CHARACTER_LENGTH(str) - понимает многобайтные символы
  • LOCATE(подстрока,строка[,начальная-позиция]), POSITION(подстрока IN строка), INSTR(строка,подстрока) - понимает многобайтные символы
  • LPAD(строка,длина,строка-заполнитель) - дополнить строку слева до достижения указанной длины
  • RPAD(строка,длина,строка-заполнитель) - дополнить строку справа до достижения указанной длины
  • LEFT(строка,длина) - вернуть указанное число символов слева, понимает многобайтные символы
  • RIGHT(строка,длина) - вернуть указанное число символов справа, понимает многобайтные символы
  • SUBSTRING(строка,начальная-позиция[,длина]), SUBSTRING(строка FROM начальная-позиция[ FOR длина]), MID(строка,начальная-позиция,длина) - понимает многобайтные символы
  • SUBSTRING_INDEX(строка,разделитель,счетчик) - подстрока до указанного числа разделителей (если число отрицательное, то справа; понимает многобайтные символы)
  • LTRIM(строка) - удаление левых пробелов
  • RTRIM(строка) - удаление правых пробелов
  • TRIM([[BOTH | LEADING | TRAILING] [префикс] FROM] строка) - если префикс не указан, то удаляются пробелы)
  • SPACE(число) - указанное число пробелов
  • REPLACE(строка,что,на-что) - понимает многобайтные символы
  • REPEAT(str,count)
  • REVERSE(str) - понимает многобайтные символы
  • INSERT(строка,позиция,длина,новая-строка) - на самом деле не вставка, а замена; понимает многобайтные символы
  • ELT(N,str1,str2,...) - возвращает N-ю строку
  • FIELD(str,str1,str2,str3,...) - возвращает индекс строки str в списке str1,...
  • FIND_IN_SET(строка,список) - номер строки в списке (строки через запятую или колонка типа SET)
  • MAKE_SET(биты,строка1,строка2,...) - конкатенирует строки в список через запятую, используются только строки для которых взведен бит
  • EXPORT_BIT(биты,on-строка,off-строка[,разделитель,число-бит]) - EXPORT_SET(5,'Y','N',',',4) выдает 'Y,N,Y,N'
  • LCASE(строка) - переводит все буквы в нижний регистр (строчные)
  • LOWER(строка) - переводит все буквы в нижний регистр (строчные)
  • UCASE(строка) - переводит все буквы в верхний регистр (прописные)
  • UPPER(строка) - переводит все буквы в верхний регистр (прописные)
  • LOAD_FILE(полное-имя-файла) - содержимое файла возвращается как строка, необходимо иметь привилегии работы с файлами; размер файла ограничен max_allowed_packet
• работа со временем и датами
  • DAYOFWEEK(дата) - воскресенье: 1, понедельник: 2 и т.д.
  • WEEKDAY(дата) - понедельник: 0, вторник: 1 и т.д.
  • DAYOFMONTH(дата)
  • DAYOFYEAR(дата)
  • MONTH(дата)
  • DAYNAME(дата) - в виде: 'Thursday'
  • MONTHNAME(дата) - в виде 'February'
  • QUARTER(дата)
  • WEEK(дата) - от 0 до 53; первый день недели - воскресенье
  • WEEK(дата,начало-недели) - если начало-недели равно 0, то неделя начинается с воскресенья, если равно 1, то с понедельника
  • YEAR(дата)
  • YEARWEEK(дата[,начало-недели]) - год и номер недели
  • HOUR(время)
  • MINUTE(время)
  • SECOND(время)
  • PERIOD_ADD(P,N) - добавить N месяцев к периоду P (P в формате YYYYMM)
  • PERIOD_DIFF(P1,P2) - число месяцев между периодами
  • DATE_ADD(дата,INTERVAL выражение единица-измерения) или ADDDATE(дата,INTERVAL выражение единица-измерения) или "время" + INTERVAL выражение единица-измерения: добавить интервал к дате/времени
    где единицы-измерения:
    • SECOND
    • MINUTE
    • HOUR
    • DAY
    • MONTH ('1998-01-30' + Interval 1 month => '1998-02-28')
    • YEAR
    • MINUTE_SECOND ("минут:секунд")
    • HOUR_MINUTE ("часов:минут")
    • DAY_HOUR ("дней часов")
    • YEAR_MONTH ("лет-месяцев")
    • HOUR_SECOND ("часов:минут:секунд")
    • DAY_MINUTE ("дней часов:минут")
    • DAY_SECUND ("дней часов:минут:секунд")
  • DATE_SUB(дата,INTERVAL выражение единица-измерения) или SUBDATE(дата,INTERVAL выражение единица-измерения) или "время" - INTERVAL выражение единица-измерения: вычесть интервал из даты/времени
  • EXTRACT(единица-измерения FROM дата) - возвращает интервал указанного типа
  • TO_DAYS(дата) - возвращает номер дня с 0-го года (не учитывает момент перехода на Грегорианский календарь)
  • FROM_DAYS(N) - обратная к TO_DAYS()
  • DATE_FORMAT(дата,формат) - преобразует дату в соответствии с форматом (см. strftime(3), кроме отсутствия i18n: %M - полное английское имя месяца; %W - полное английское имя дня недели; %D - день месяца с английским суффиксом; %c - номер месяца от 1 до 12; %h - час от 01 до 12; %i - минуты от 00 до 59)
  • TIME_FORMAT(время,формат) - аналогично DATE_FORMAT, но ограниченно часами, минутами и секундами
  • CURDATE() - '1997-12-15' или 19971215 в зависимости от контекста
  • CURRENT_DATE - синоним CURDATE()
  • CURTIME() - '23:50:26' или 235026 в зависимости от контекста
  • CURRENT_TIME - синоним CURTIME()
  • NOW() или SYSDATE() или CURRENT_TIMESTAMP - '1997-12-15 23:50:26' или 19971215235026 в зависимости от контекста
  • UNIX_TIMESTAMP([дата]) - число секунд с 1 января 1970 GMT, дата в локальном времени
  • FROM_UNIXTIME(timestamp[,формат]) - обратная к UNIX_TIMESTAMP()
  • SEC_TO_TIME(секунд) - преобразует секунды в часы:минуты:секунды
  • TIME_TO_SEC(время) - преобразует часы:минуты:секунды в секунды
• разное
  • DATABASE() - имя текущей БД
  • USER() или SYSTEM_USER() или SESION_USER() - текущее имя пользователя и хост ('root@localhost')
  • PASSWORD(строка) - перевод пароля во внутренний вид (не UNIX!)
  • ENCRYPT(строка[,соль]) - UNIX-овый преобразование пароля
  • ENCODE(строка,пароль) - шифровка (какой алгоритм?)
  • DECODE(шифрованная-строка,пароль) - расшифровка
  • MD5(строка)
  • LAST_INSERT_ID([выражение])
  • FORMAT(X,D) - вывод числа в виде 'xxx,xxx.xx', где D - число цифр после точки
  • VERSION() - '3.23.37-log'
  • CONNECTION_ID()
  • GET_LOCK(имя-замка,timeout-секунд) - возвращает 1 при успехе, 0 - при timeout, NULL - при ошибке; освобождается при RELEASE_LOCK(), новом GET_LOCK() или завершении потока
  • RELEASE_LOCK(имя-замка) - возвращает 1 при успехе; 0 - если замок был закрыт не этим потоком; NULL - если замок не существует
  • INET_NTOA(выражение) - преобразование IP-адреса в сетевом формате в "точечный" формат
  • INET_ATON(выражение) - преобразование IP-адреса из "точечного" формата в целое с сетевым порядком байт
  • MASTER_POS_WAIT(имя-журнала,позиция) - синхроницация репликации
• функции для group by (использование данных функций в операторе без group by влечет группирование всех строк)
  • COUNT(выражение) - число не-NULL значений в извлеченных строках
  • COUNT(*) - число извлеченных строк
  • COUNT(DISTINCT выражение,[выражение...]) - число различных не-NULL значений
  • AVG(выражение) - среднее
  • MIN(выражение), MAX(выражение)
  • SUM(выражение) - сумма (если не извлечено ни одной строки, то NULL)
  • STD(выражение) или STDDEV(выражение) - стандартное отклонение
  • BIT_OR(выражение) - побитовое или
  • BIT_AND(выражение) - побитовое И

Утилиты администрирования

mysqladmin

• --force (не запрашивать подтверждений и не обращать внимания на ошибки)
• --compress (использовать сжатие в протоколе обмена)
• --host=имя
• --password[=пароль]
• --port=порт
• --sleep=секунд (повторять команду с интервалом)
• --socket=имя-файла
• --user=имя
• --set-variable имя=значение

• create имя-БД
• drop имя-БД
• extended-status
• flush-hosts (сброс кеша DNS?)
• flush-logs
• flush-status (сброс статистики?)
• flush-tables (запись всех буферизованных изменений)
• flush-threads
• flush-privileges (заново считать таблицы привилегий)
• reload (то же самое)
• kill номер-процесса
• password новый-пароль
• ping (работает ли mysqld)
• processlist
• refresh (flush-tables + flush-logs)
• shutdown
• status
• variables
• version

isamchk

isamlog

mysqlaccess

mysqldump

mysqlimport

mysqlshow

xmysqladmin

Mysql_watchdog

www интерфейс

Mysql-webadmin

Mysqladm

Клиентские приложения

mysql

xmysql

Процедуры

Резервирование данных

• сайт разработчиков (есть сравнение возможностей и тесты производительности)
• документация (700 страниц!!!): PS, PDF
• документация с комментариями пользователей
• Рэнди Джей Яргер, Джордж Риз, Тим Кинг: MySQL и mSQL (O'Reilly и Символ-плюс)
• сравнение с другими РСУБД
• описание 3.20.29 по-русски
• установка и администрирование MySQL на русском
• оптимизация MySQL 3.23
• Setting Up a MySQL Based Website
• PHP/MySQL Tutorial
• announce@lists.mysql.com