mysql基础

这几天又重新回顾了下mysql的基础知识。在这里做个备份。

以下是看《mysql_administrators_bible》摘录及总结的内容。

 

 

1、字符集
To summarize the confusing connection/client/results relationship, consider this: A
client sends a statement in character_set_client, which is converted to
character_set_connection and collation_connection by mysqld. After query execution, results
are converted by mysqld to character_set_results. Almost all of the time, the
character_set_client and character_set_results variables will be set with the same
value — the character set of the client.

 

2.mysql的一些限制
(1)
CAST() and CONVERT()—According to the SQL standard, the CAST() and
CONVERT() functions should be able to cast to any data type. However, in MySQL,
CAST()and CONVERT() cannot be used to cast a number to the REAL or BIGINT
data type.
(2)
MySQL does not support static cursors. In MySQL, all cursors are dynamic.
(3)
MySQL does not support domains or domain constraints. The CREATE DOMAIN, ALTER
DOMAIN, and DROP DOMAIN statements are not supported, and DOMAIN permissions cannot
be granted using GRANT.
(4)
MySQL does not support dynamically prepared statements using EXECUTE IMMEDIATE.
Regular prepared statements are supported, as is the EXECUTE statement without
the IMMEDIATE qualifier.

 

 

3.
■ Delete the existing record and insert a new record (as with REPLACE)
■ Update the existing record (as with ON DUPLICATE KEY UPDATE)
■ Do nothing (as with IGNORE)

 

 

4.mysql的扩展
(1) 别名
■ BEGIN and BEGIN WORK are aliases for START TRANSACTION.
■ DROP PREPARE stmt_prep is a synonym for DEALLOCATE PREPARE stmt_prep.
■ EXPLAIN tbl_name is an alias for SHOW COLUMNS FROM tbl_name.
■ num1 % num2 is the same as MOD(num1,num2).

(2)ALTER TABLE 的扩展
mysql> use sakila;
Database changed
mysql> ALTER TABLE film
-> ADD COLUMN origin_country SMALLINT(5) UNSIGNED
-> NOT NULL DEFAULT 103,
-> ADD INDEX idx_fk_origin_country (origin_country),
-> ADD CONSTRAINT fk_film_country FOREIGN KEY (origin_country)
-> REFERENCES country(country_id);

■ ADD/ALTER/DROP COLUMN
■ ADD/DROP PRIMARY/UNIQUE/FOREIGN KEY
■ ADD FULLTEXT INDEX
■ ADD INDEX
■ ADD SPATIAL INDEX

■ CONVERT TO CHARACTER SET charset_name
■ CONVERT TO CHARACTER SET charset_name COLLATION collation_name
■ DISABLE KEYS
■ ENABLE KEYS
■ IGNORE 

(3)CREATE extensions
 CREATE DATABASE IF NOT EXISTS test;
 CREATE OR REPLACE VIEW view_name

(4)MySQL extends DML (Data Manipulation Language — INSERT, REPLACE, UPDATE, and DELETE statements)
■ LOAD DATA INFILE—The LOAD DATA INFILE command is used to load data from a text
file created by the SELECT INTO OUTFILE command.

mysql> SELECT * FROM rental INTO OUTFILE ’rental.sql’;
mysql> LOAD DATA INFILE ’/tmp/rental.sql’ INTO TABLE rental;
■ LOAD XML INFILE —The LOAD XML INFILE command can be used to load XML data
into tables. The text file for input can be any XML file.

shell> mysql --xml -e ’SELECT * FROM sakila.film’ > /tmp/film.xml
mysql> load xml infile ’/tmp/film.xml’ into table film;

 

 

5、变量
(1) 用户自定义变量 @variables
(2) 系统变量 @@variables
mysql> SELECT @@global.max_allowed_packet,@@session.max_allowed_packet

 

 

6.SHOW 扩展
mysql> show profile;
mysql> SET profiling=1;
mysql> SELECT COUNT(*) FROM sakila.film;
mysql> SHOW PROFILE;  查看分析结果

mysql> SHOW PROFILE CPU, SOURCE FOR QUERY 1 LIMIT 2 OFFSET 0;

show profile 参数:
  选项           说明
BLOCK IO         Status, Duration, Block_ops_in, Block_ops_out
CONTEXT SWITCHES  Status, Duration, Context_voluntary,Context_involuntary
CPU        Status, Duration, CPU_user, CPU_system
IPC        Status, Duration, Messages_sent,Messages_received
MEMORY       Status, Duration
PAGE FAULTS    Status, Duration, Page_faults_major,Page_faults_minor
SOURCE       Status, Duration, Source_function, Source_file,Source_line
SWAPS       Status, Duration, Swaps

 

 

7.碎片
数据及索引可能产生碎片。
mysql> OPTIMIZE TABLE film_text;

Tables using the InnoDB storage engine map the OPTIMIZE TABLE command to an ALTER TABLE command.

innodb_file_per_table ？

 

 

8、Maintaining table statistics 维护表统计
The maintenance of data and indexes should include maintaining the metadata that the server
stores about table statistics. This is important because the query optimizer uses this information
in choosing which indexes, if any, to use when executing a query.

重新统计
mysql> ANALYZE TABLE film;

 

 

9.mysql 数据类型
(1)char
如果字段类型是char型，当存储的数据长度，小于字段定义长度时，会填充空格。这样取数据的时候会自动去掉后面的空格。
若：mysql> INSERT INTO string_type (fw1) VALUES (’a’),(’a ’);

mysql> SELECT CONCAT(’/’,fw1,’/’), CHAR_LENGTH(fw1) FROM string_type;
+---------------------+-------------+
| CONCAT(’/’,fw1,’/’) | LENGTH(fw1) |
+---------------------+-------------+
| /a/         | 1      |
| /a/         | 1      |

这样第二条数据就不是存入的数据了。

mysql> SELECT @@session.sql_mode;
+----------------------------------------------------------------+
| @@session.sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SET SESSION sql_mode = ’PAD_CHAR_TO_FULL_LENGTH,STRICT_TRANS_
TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(’/’,fw1,’/’), LENGTH(fw1) FROM string_type;
+---------------------+-------------+
| CONCAT(’/’,fw1,’/’) | LENGTH(fw1) |
+---------------------+-------------+
| /a    /       | 10          |
| /a    /          | 10      |

为了避免以上问题，可以用varchar类型代替char

(2)TEXT 类型的最大字符长度
■ TINYTEXT — Up to 255 bytes, 1 byte overhead
■ TEXT   — Up to 64 Kb, 2 bytes overhead
■ MEDIUMTEXT— Up to 16 Mb, 3 bytes overhead
■ LONGTEXT — Up to 4 Gb, 4 bytes overhead

(3)二进制大对象字符串类型 Binary Large Object String Types
ISO SQL:2003 standard中有两种类型:
■ BINARY LARGE OBJECT(length)
■ BLOB(length)

而在mysql中只支持第二种 BLOB(length) 且分类如下:
■ TINYBLOB
■ MEDIUMBLOB
■ LONGBLOB
■ BINARY(length)
■ VARBINARY(length)

Binary string types are byte strings. Character strings are ordered lexically; binary strings are
ordered by each byte’s value.

几种类型的最大存储:
■ TINYBLOB — Up to 255 bytes, 1 byte overhead
■ BLOB   — Up to 64 Kb, 2 bytes overhead
■ MEDIUMBLOB— Up to 16 Mb, 3 bytes overhead
■ LONGBLOB — Up to 4 Gb, 4 bytes overhead

(4)数字类型
■ NUMERIC(g,f)
■ DECIMAL(g,f) can be abbreviated as DEC
■ SMALLINT - 2 bytes
■ INTEGER can be abbreviated as INT - 3 bytes
■ BIGINT - 8 bytes

■ FLOAT(p) - 4 bytes
■ REAL
■ DOUBLE PRECISION - 8 bytes

■ TINYINT - 1 byte
■ MEDIUMINT - 4 bytes
■ BIT(x)
■ SERIAL

AUTO_INCREMENT 自增型
自增型字段必须是唯一索引(UNIQUE index),非空(NOT NULL)
改变自增字段的初始值：可以在配置文件里或者session或者全局变量变量的 auto_increment_offset 值
设置每次的增量值可以改变 auto_increment_increment 变量的值

若要改变已经存在的表的自增字段初始值，用一下语句:
mysql> ALTER TABLE table_name AUTO_INCREMENT=20;

(5)布尔类型 Boolean Types
mysql 中用TINYINT(1) 来实现bool

mysql> CREATE TABLE boolean_test ( bt1 BOOLEAN, bt2 BOOL, bt3 TINYINT(1));
Query OK, 0 rows affected (0.19 sec)
mysql> SHOW CREATE TABLE boolean_test;
*************************** 1. row ***************************
Table: boolean_test
Create Table: CREATE TABLE `boolean_test` (
`bt1` tinyint(1) DEFAULT NULL,
`bt2` tinyint(1) DEFAULT NULL,
`bt3` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

(6)时间类型 Datetime Types
ISO SQL:2003 标准定义了3个时间类型:
■ DATE
■ TIME(p)
■ TIMESTAMP(p)
并扩展了两个属性:
■ WITH TIME ZONE
■ WITHOUT TIME ZONE

PS: mysql 不支持这两个属性值，也不支持 TIME 和 TIMESTAMP 的精度参数

mysql还扩展了两个时间类型:
■ YEAR
■ DATETIME

其中YEAR类型可以表示成两种方式:YEAR(2) 和 YEAR(4).
对于YEAR(2) 00-69表示2000-2069
            70-99表示1970-1999

各种时间类型的表示范围及所占用的字节数
       Supported Range (Guaranteed to Work)       Size     Zero Value
---------------------------------------------------------------------------------------      
DATE     ‘1000-01-01’ to ‘9999-12-31’           3 bytes  ‘0000-00-00’
DATETIME   ‘1000-’01-01 00:00:01’ to ‘9999-12-31 23:59:59’ 8 bytes  ‘0000-00-00 00:00:00’
TIMESTAMP  ‘1970-01-01 00:00:00’ to ‘2038-01-18 22:14:07’  4 bytes  ‘0000-00-00 00:00:00’
TIME     ‘–838:59:59’ to ‘838:59:58’           3 bytes  ‘00:00:00’
YEAR(2)   00 to 99                     1 byte   ‘00’’
YEAR(4)   1901 to 2155                   1 byte   ‘0000’

sql 语句中允许的时间格式:

对于 DATETIME 和 TIMESTAMP
■ YYYY-mm-dd HH:ii:ss
■ yy-mm-dd HH:ii:ss
■ YYYYmmdd
■ yymmdd —As a string or as a number, that is, ’20090508’ or 20090508
■ YYYYmmddHHiiss—As a string or as a number
■ yymmddHHiiss—As a string or as a number
■ Actual datetime type values, such as the return from CURRENT_TIMESTAMP and CURRENT_DATE()

对于 TIME
■ dd HH:ii:ss
■ dd HH
■ dd HH:ii
■ HH:ii:ss
■ HH:ii
■ ss—As a string or as a number
■ HHiiss—As a string or as a number
■ iiss— As a string or as a number
■ Actual TIME values, such as the return from CURRENT_TIME()

时间字段的自动更新(Automatic updates)
TIMESTAMP 类型的字段允许指定属性 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT TIMESTAMP 自动更新。
PS:每张表中最多只允许有一个时间自动更新的字段。

 

 

10.选择sql模式(Choosing SQL Modes)
查看当前服务器的全局sql模式
SHOW GLOBAL VARIABLES LIKE ’sql_mode’;
SELECT @@global.sql_mode;
SELECT Variable_name, Variable_value FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE Variable_name=’sql_mode’;

查看当前session的sql模式
SHOW SESSION VARIABLES LIKE ’sql_mode’;
SELECT @@session.sql_mode;
SELECT Variable_name, Variable_value FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name=’sql_mode’;

 

 

11、如何在已有的数据中找出最优的字段类型
PROCEDURE ANALYSE() 该函数有两个可选的参数:
■ The first argument is the maximum number of values to consider for an ENUM data type
(enumerated list). The default is 256; in practice, a lower value is usually preferred. Maintaining
an ENUM data type can become unwieldy after a few dozen values.
■ The second argument is the amount of memory in bytes to use per field to determine an
optimal data type. The default is 8192.

eg:
mysql> SELECT title FROM film PROCEDURE ANALYSE(16,256)/G
*************************** 1. row ***************************
Field_name: sakila.film.title
Min_value: ACADEMY DINOSAUR
Max_value: ZORRO ARK
Min_length: 8
Max_length: 27
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 14.2350
Std: NULL
Optimal_fieldtype: VARCHAR(27) NOT NULL
1 row in set (0.00 sec)