MySQL的数据类型
MariaDB [(none)]> show character set; #查看支持的字符集MariaDB [(none)]> show collation; #查看支持的字符集
MariaDB [(none)]> select last_insert_id(); #查看最后一个插入数值的ID+------------------+| last_insert_id() |+------------------+| 0 |+------------------+1 row in set (0.00 sec)
MariaDB [tt]> create table enum_set (en ENUM(‘a‘,‘‘,‘1‘),se SET(‘a‘,‘1‘));- MariaDB [tt]> insert into enum_set (en,en) values("a","a");
MariaDB [tt]> insert into enum_set (en,se) values("a","a");- MariaDB [tt]> insert into enum_set (en,se) values("a","b");
- MariaDB [tt]> insert into enum_set (en,se) values("b","a");
- MariaDB [tt]> select * from enum_set;
+------+------+| en | se |+------+------+| a | a || a | || | a |+------+------+3 rows in set (0.00 sec)
MariaDB [tt]> show global variables like "sql_mode"; #sql_mode查询- +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.00 sec)
- MariaDB [tt]> show global variables like "sql_%"; #模糊匹配查询方式 +------------------------+--------------------------------------------+ | Variable_name | Value | +------------------------+--------------------------------------------+ | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | +------------------------+--------------------------------------------+ 12 rows in set (0.00 sec)
MariaDB [tt]> set global sql_mode=‘MODE‘;MariaDB [tt]> set @@global.sql_mode=‘MODE‘;MariaDB [tt]> set session sql_mode=‘MODE‘;MariaDB [tt]> set @@session.sql_mode=‘MODE‘;
MariaDB [mydbl]> create database ttt;MariaDB [mydbl]> use ttt;- MariaDB [ttt]> create table t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,name CHAR(5) NOT NULL);
- MariaDB [ttt]> insert into t1 (name) values(‘wang‘),(‘xuexusd‘); #没修改之前会有警告，但不报错
- MariaDB [mydbl]> set sql_mode=‘TRADITIONAL‘; #改之后，超过字符限制就会报错
- Query OK, 2 rows affected, 2 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 2
- MariaDB [ttt]> insert into t1 (name) values(‘wang‘),(‘xufsdsexu‘);
- ERROR 1406 (22001): Data too long for column ‘name‘ at row 1 #错误信息
MariaDB [ttt]> help create;- Many help items for your request exist. To make a more specific request, please type ‘help <item>‘, where <item> is one of the following topics: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SPATIAL
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition,...)[table_options][partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)][table_options][partition_options]select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{ LIKE old_tbl_name | (LIKE old_tbl_name) }
MariaDB [ttt]> desc t1; #查看表结构 +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(5) | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)MariaDB [ttt]> show tables; #查看表个数 +---------------+ | Tables_in_ttt | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec)- MariaDB [ttt]> show table status like ‘t1‘\G; #查看表状态 *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 #平均每行宝库奥的字节数 Data_length: 16384 #表中数据的大小 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 3 Create_time: 2017-01-07 13:51:08 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci #排序规则 Checksum: NULL #表的校验和（性能优化） Create_options: #额外选项 Comment: #注释信息 1 row in set (0.00 sec)
原文：http://www.cnblogs.com/wanghui1991/p/6261848.html