MySQL

от ILuxWiki

Съдържание 1 Инсталация 1.1 FreeBSD 2 Администрация 2.1 /etc/my.cnf 2.2 Foreign key 2.3 Създаване на потребител 2.4 Смяна на парола 3 Работа с таблици 3.1 Преименуване на колона 3.2 AUTO_INCREMENT 3.3 SQL_CALC_FOUND_ROWS 3.4 Find Dupblicates 4 Работа със времеви стойности 5 Оптимизиране на базите данни 6 Encoding

Инсталация

FreeBSD

Опций за постигане на по добра скорост:

 WITH_PROC_SCOPE_PTH=yes BUILD_OPTIMIZED=yes BUILD_STATIC=yes

Администрация

/etc/my.cnf

 [mysqld]
 bind-address    = 127.0.0.1

Foreign key

SET FOREIGN_KEY_CHECKS=0;

Създаване на потребител

GRANT ALL PRIVILEGES ON DM_TRANSCARD.* TO 'translo'@'localhost' IDENTIFIED BY 'w7QWEFds';

Смяна на парола

 mysqld_safe --skip-grant-tables

Работа с таблици

Преименуване на колона

Не може просто да се преименува името, трябва да се зададе и от ккаъв тип ще бъде и всички останали параметри.

 mysql> explain ip2group;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | ip    | int(11)     | NO   |     | 0       |       | 
 | group | smallint(6) | NO   |     | 0       |       | 
 +-------+-------------+------+-----+---------+-------+

• промяна на ip към ip_list_id

 ALTER TABLE IP2GROUP CHANGE ip ip_list_id INT(11) NOT NULL DEFAULT 0;

AUTO_INCREMENT

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

• SET INSERT_ID = value

Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

• LAST_INSERT_ID = value

Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function.

SQL_CALC_FOUND_ROWS

SELECT  SQL_CALC_FOUND_ROWS  *  FROM  `mp3list`  
WHERE filename LIKE  "%metall%" LIMIT 1

SELECT FOUND_ROWS() as count;

Find Dupblicates

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Работа със времеви стойности

• SEC_TO_TIME(seconds) - превръщане на секундите във време, внимателно с това защото може да се получат резултати които няма да ви се харесат:

mysql> select sec_to_time(1118354244); # 1118354244 = unixtime
+-------------------------+
| sec_to_time(1118354244) |
+-------------------------+
| 310653:57:24            |
+-------------------------+
1 row in set (0.00 sec)

• Извличане на записи в интервала от ДНЕС до 30 дена назад:

SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

• Изтриване на всички записи по-стари от 3 часа:

DELETE FROM someting WHERE DATE_SUB(NOW(),INTERVAL 3 HOUR) >= date_col;

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html - подробна справка

• оптимизиране по скорост

Several times i have come to a followng date/time problem: In the table i am storing both date and time information in the datetime column. Querying, I want to receive COUNTed results grouped by date, and not date and time. I came to the easy solution:

SELECT DATE_FORMAT(postdate, '%Y-%m-%d') AS dd, COUNT(id) FROM MyTable GROUP BY dd;

I suppose this solution to be quite slow (date formatting).

Later, i 'upgraded' this query to use the string function:

SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;

knowing, that the result is in the fixed format. Works faster.

• оптимизиране, ама друг път!

Забавянето на горния пример се дължи на DATE_FORMAT(). Затова SUBSTRING() се явява "спасител" за скороста на изпълнение на заявката При следващия пример това не помага. EXTRACT() работи по бързо от SUBSTRING().

 mysql> select EXTRACT(month FROM time) as mon from old_free_2005 group by mon;
 +------+
 | mon  |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    4 |
 |    5 |
 +------+
 5 rows in set (2.05 sec)

 mysql> select SUBSTRING(time,6,2) as mon from old_free_2005 group by mon;
 +-----+
 | mon |
 +-----+
 | 01  |
 | 02  |
 | 03  |
 | 04  |
 | 05  |
 +-----+
 5 rows in set (2.30 sec)

Оптимизиране на базите данни

• анализ на структурата на таблицата и показване на препоръчителни стойности на колоните.

select * from table_name procedure analyse()\G

Encoding

• Промяна от latin1 в utf8

mysqldump -u root -p --opt --default-character-set=latin1 --skip-set-charset  DBNAME > DBNAME.sql
mysql -u root -p --default-character-set=utf8 DBNAME < DBNAME.sql

• Смяна на енкодинга по подразбиране при клиент/сървър ( за да не се налага писането на SET NAMES 'utf8' )

Добавя се в my.cnf, [mysqld] секцията:

skip-character-set-client-handshake
character-set-server=utf8
collation-server=utf8_general_ci

• Може и да стане със следните редове в mysql конзола:

ALTER TABLE t1 MODIFY a BLOB;
UPDATE t1 SET a=CONVERT(CAST(a as CHAR CHARACTER SET utf8) using latin1);
ALTER TABLE t1 MODIFY a TEXT CHARSET utf8;