MySQL指南——数据库操作

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。


数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度浮点数值
DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值,时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。

创建数据库

mysql 创建数据库是会选择collation

mysql的collation大致的意思就是字符序。首先字符本来是不分大小的,那么对字符的>, = , < 操作就需要有个字符序的规则。collation做的就是这个事情,你可以对表进行字符序的设置,也可以单独对某个字段进行字符序的设置。一个字符类型,它的字符序有多个,比如:

下面是UTF8对应的字符序。

1
2
3
4
5
6
7
8
9
utf8_general_ci utf8    33  Yes Yes 1
utf8_bin utf8 83 Yes 1
utf8_unicode_ci utf8 192 Yes 8
utf8_icelandic_ci utf8 193 Yes 8
utf8_latvian_ci utf8 194 Yes 8
utf8_romanian_ci utf8 195 Yes 8
utf8_slovenian_ci utf8 196 Yes 8
utf8_polish_ci utf8 197 Yes 8
........

mysql的字符序遵从命名惯例。以_ci(表示大小写不敏感),以_cs(表示大小写敏感),以_bin(表示用编码值进行比较)。

如果对字符大小敏感的话,最好将数据库中默认的utf8_general_ci设置为utf8_bin。

创建表

遇到错误

1
2
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs

一个varchar/char占3个字节 每行数据不能超过65535个字节 所以每行的varchar/char不能超过21845。

全文搜索

索引列

对某些列,进行全文索引:fulltext(col1, col2)

1
2
3
4
5
6
7
8
9
create table  if not exists repos (
id bigint(6),
star_owner char(200) BINARY CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
description mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
full_name varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
name char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
primary key (id, star_owner),
fulltext(full_name, description)
) ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

或者,在创建表后:

1
ALTER TABLE `repos` ADD FULLTEXT(`full_name`,`description`);

搜索

1
SELECT full_name, description FROM repos WHERE MATCH (full_name,description) AGAINST('beefun');

Column ‘description’ cannot be part of FULLTEXT index

该错误,是在执行以下语句时:

1
ALTER TABLE `repos` ADD FULLTEXT(`full_name`,`description`);

full_namedescription两者的chareset不一致。注意修改成一致即可。

☞ Stackoverflow

SQL

错误

Parameter index out of range (2 > number of parameters, which is 0)

1
2
3
4
5
6
7
8
9
   public static final String SELECT_OWNER_AND_TAG_LANGUAGE_ORDER_WITH_SEARCH =
"<script>"
+ "SELECT * FROM starrepos "
+"WHERE star_owner=#{owner} "
+"<if test='language!=null and language!=\"all\"'> AND language=#{language} </if> "
+"<if test='tag!=null and tag!=\"all\" '> AND FIND_IN_SET(#{tag}, star_tags) </if> "
+"<if test='search!=null'> AND LOWER(full_name) LIKE \'%#{search}%\' OR LOWER(description) LIKE \'%#{search}%\' </if> "
+"<if test='sort!=null and direction!=null'> ORDER BY ${sort} ${direction} </if> "
+"</script>";

执行上面SQL会报上述错误。只需要将#{参数}修改为${参数}

1
+"<if test='search!=null'> AND LOWER(full_name) LIKE \'%#{search}%\' OR LOWER(description) LIKE \'%#{search}%\' </if> "

参考

  1. W3Cschool mysql教程