char、varchar的区别

  1. mysql> create database gaokao;
  2. Query OK, 1 row affected (0.00 sec)
  3. mysql> show create database gaokao\G
  4. *************************** 1. row ***************************
  5. Database: gaokao
  6. Create Database: CREATE DATABASE `gaokao` /*!40100 DEFAULT CHARACTER SET utf8 */
  7. 1 row in set (0.00 sec)
  8. mysql> create table yuwen(name varchar(20),mobil char(11));
  9. Query OK, 0 rows affected (0.04 sec)
  10. mysql> show create table yuwen\G
  11. *************************** 1. row ***************************
  12. Table: yuwen
  13. Create Table: CREATE TABLE `yuwen` (
  14. `name` varchar(20) DEFAULT NULL,
  15. `mobil` char(11) DEFAULT NULL
  16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  17. 1 row in set (0.00 sec)
  18. mysql> insert into yuwen values('aa','13887651111'),(' bb',' 1233331234'),('cc ','12345678900 ');
  19. Query OK, 3 rows affected (0.01 sec)
  20. Records: 3 Duplicates: 0 Warnings: 0
  21. mysql> select * from yuwen;
  22. +------+-------------+
  23. | name | mobil |
  24. +------+-------------+
  25. | aa | 13887651111 |
  26. | bb | 1233331234 |
  27. | cc | 12345678900 |
  28. +------+-------------+
  29. 3 rows in set (0.00 sec)
  30. ###错误的示例
  31. mysql> select concat(''',name,''') from yuwen;
  32. +----------------------+
  33. | concat(''',name,''') |
  34. +----------------------+
  35. | ',name,' |
  36. | ',name,' |
  37. | ',name,' |
  38. +----------------------+
  39. 3 rows in set (0.00 sec)
  40. mysql> select concat("'",name,"'") from yuwen;
  41. +----------------------+
  42. | concat("'",name,"'") |
  43. +----------------------+
  44. | 'aa' |
  45. | ' bb' |
  46. | 'cc ' |
  47. +----------------------+
  48. 3 rows in set (0.00 sec)
  49. mysql> select concat("'",name,"'"),concat("'",mobil,"'") from yuwen;
  50. +----------------------+-----------------------+
  51. | concat("'",name,"'") | concat("'",mobil,"'") |
  52. +----------------------+-----------------------+
  53. | 'aa' | '13887651111' |
  54. | ' bb' | ' 1233331234' |
  55. | 'cc ' | '12345678900' |
  56. +----------------------+-----------------------+
  57. 3 rows in set (0.00 sec)
  58. mysql> help concat;
  59. Name: 'CONCAT'
  60. Description:
  61. Syntax:
  62. CONCAT(str1,str2,...)
  63. Returns the string that results from concatenating the arguments. May
  64. have one or more arguments. If all arguments are nonbinary strings, the
  65. result is a nonbinary string. If the arguments include any binary
  66. strings, the result is a binary string. A numeric argument is converted
  67. to its equivalent nonbinary string form.
  68. CONCAT() returns NULL if any argument is NULL.
  69. URL: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html
  70. Examples:
  71. mysql> SELECT CONCAT('My', 'S', 'QL');
  72. -> 'MySQL'
  73. mysql> SELECT CONCAT('My', NULL, 'QL');
  74. -> NULL
  75. mysql> SELECT CONCAT(14.3);
  76. -> '14.3'
  77. mysql> select * from yuwen;
  78. +------+-------------+
  79. | name | mobil |
  80. +------+-------------+
  81. | aa | 13887651111 |
  82. | bb | 1233331234 |
  83. | cc | 12345678900 |
  84. +------+-------------+
  85. 3 rows in set (0.00 sec)

datetime、timestamp的区别



mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-06-07 09:48:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2021 |
+-------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            49 |
+---------------+
1 row in set (0.00 sec)

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> alter table yuwen add exam_time datetime;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from yuwen;
+------+-------------+-----------+
| name | mobil       | exam_time |
+------+-------------+-----------+
| aa   | 13887651111 | NULL      |
|  bb  |  1233331234 | NULL      |
| cc   | 12345678900 | NULL      |
+------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> insert into yuwen values('dd','12345678901',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+
| name | mobil       | exam_time           |
+------+-------------+---------------------+
| aa   | 13887651111 | NULL                |
|  bb  |  1233331234 | NULL                |
| cc   | 12345678900 | NULL                |
| dd   | 12345678901 | 2021-06-07 09:52:16 |
+------+-------------+---------------------+
4 rows in set (0.00 sec)

mysql> update yuwen set mobil = '13678900987' where name = 'dd';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+
| name | mobil       | exam_time           |
+------+-------------+---------------------+
| aa   | 13887651111 | NULL                |
|  bb  |  1233331234 | NULL                |
| cc   | 12345678900 | NULL                |
| dd   | 13678900987 | 2021-06-07 09:52:16 |
+------+-------------+---------------------+
4 rows in set (0.00 sec)


mysql> alter table yuwen add modify_time timestamp;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+
| name | mobil       | exam_time           | modify_time         |
+------+-------------+---------------------+---------------------+
| aa   | 13887651111 | NULL                | 2021-06-07 09:54:48 |
|  bb  |  1233331234 | NULL                | 2021-06-07 09:54:48 |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 09:54:48 |
+------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)


mysql> update yuwen set mobil = '12333312349' where name = ' bb';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+
| name | mobil       | exam_time           | modify_time         |
+------+-------------+---------------------+---------------------+
| aa   | 13887651111 | NULL                | 2021-06-07 09:54:48 |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 09:54:48 |
+------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)

int(4)、int(10)的区别

对数据的存储范围没有任何区别,配合zerofill可以看出不足int(x) x宽度的时候会补0

mysql> create table iwidth(aid int(4) zerofill,bid int(10) zerofill);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into iwidth select 1,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into iwidth select 1234,123456789;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into iwidth select 1234,1234567890;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from iwidth;
+------+------------+
| aid  | bid        |
+------+------------+
| 0001 | 0000000002 |
| 1234 | 0123456789 |
| 1234 | 1234567890 |
+------+------------+
3 rows in set (0.00 sec)

mysql> insert into iwidth select 1234567890,1234567890;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from iwidth;
+------------+------------+
| aid        | bid        |
+------------+------------+
|       0001 | 0000000002 |
|       1234 | 0123456789 |
|       1234 | 1234567890 |
| 1234567890 | 1234567890 |
+------------+------------+
4 rows in set (0.00 sec)

mysql>

enum的使用

mysql> alter table yuwen add sex char(1);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update yuwen set sex = '男' where name = 'aa';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 09:54:48 | NULL |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> update yuwen set sex = '妖' where name = 'dd';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * FROM YUWEN;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:00:36 | 妖   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> alter table yuwen modify sex enum('男','女','未知');
ERROR 1265 (01000): Data truncated for column 'sex' at row 4


mysql> select * FROM YUWEN;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:00:36 | 妖   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> help change;
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:
   ALTER TABLE
   CHANGE MASTER TO
   CHANGE REPLICATION FILTER

mysql> help alter;
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:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION
   ALTER INSTANCE
   ALTER LOGFILE GROUP
   ALTER PROCEDURE
   ALTER SERVER
   ALTER TABLE
   ALTER TABLESPACE
   ALTER USER
   ALTER VIEW
   GRANT
   SPATIAL




mysql> alter table yuwen change sex sex enum('男','女','未知');
ERROR 1265 (01000): Data truncated for column 'sex' at row 4

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:00:36 | 妖   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)



mysql> update yuwen set sex  = '女' where name = 'dd';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter table yuwen change sex sex enum('男','女','未知');
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
+------+-------------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sex` enum('男','女','未知') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('ee','13678900987',now(),'男');
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
+------+-------------+---------------------+---------------------+------+
5 rows in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('ff','13678900997',now(),'妖');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1


mysql> insert into yuwen(name,mobil,exam_time) values('ff','13678900997',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
| ff   | 13678900997 | 2021-06-07 10:11:02 | 2021-06-07 10:11:02 | NULL |
+------+-------------+---------------------+---------------------+------+
6 rows in set (0.00 sec)

mysql> select sex from yuwen;
+------+
| sex  |
+------+
| 男   |
| NULL |
| NULL |
| 女   |
| 男   |
| NULL |
+------+
6 rows in set (0.00 sec)

mysql> select sex+0 from yuwen;
+-------+
| sex+0 |
+-------+
|     1 |
|  NULL |
|  NULL |
|     2 |
|     1 |
|  NULL |
+-------+
6 rows in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('gg','13678900997',now(),'未知');
Query OK, 1 row affected (0.01 sec)

mysql> select sex from yuwen;
+------+
| sex  |
+------+
| 男   |
| NULL |
| NULL |
| 女   |
| 男   |
| NULL |
| 未知 |
+------+
7 rows in set (0.00 sec)

mysql> select sex+0 from yuwen;
+-------+
| sex+0 |
+-------+
|     1 |
|  NULL |
|  NULL |
|     2 |
|     1 |
|  NULL |
|     3 |
+-------+
7 rows in set (0.00 sec)

mysql> show create table yuwen\G
*************************** 1. row ***************************
       Table: yuwen
Create Table: CREATE TABLE `yuwen` (
  `name` varchar(20) DEFAULT NULL,
  `mobil` char(11) DEFAULT NULL,
  `exam_time` datetime DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sex` enum('男','女','未知') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from yuwen where sex = 1;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
+------+-------------+---------------------+---------------------+------+
2 rows in set (0.00 sec)

mysql> select * from yuwen where sex = 3;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| gg   | 13678900997 | 2021-06-07 10:12:32 | 2021-06-07 10:12:32 | 未知 |
+------+-------------+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql> insert into yuwen(name,mobil,exam_time,sex) values('hh','13678900997',now(),3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from yuwen;
+------+-------------+---------------------+---------------------+------+
| name | mobil       | exam_time           | modify_time         | sex  |
+------+-------------+---------------------+---------------------+------+
| aa   | 13887651111 | NULL                | 2021-06-07 10:00:15 | 男   |
|  bb  | 12333312349 | NULL                | 2021-06-07 09:57:02 | NULL |
| cc   | 12345678900 | NULL                | 2021-06-07 09:54:48 | NULL |
| dd   | 13678900987 | 2021-06-07 09:52:16 | 2021-06-07 10:07:12 | 女   |
| ee   | 13678900987 | 2021-06-07 10:09:29 | 2021-06-07 10:09:29 | 男   |
| ff   | 13678900997 | 2021-06-07 10:11:02 | 2021-06-07 10:11:02 | NULL |
| gg   | 13678900997 | 2021-06-07 10:12:32 | 2021-06-07 10:12:32 | 未知 |
| hh   | 13678900997 | 2021-06-07 10:13:41 | 2021-06-07 10:13:41 | 未知 |
+------+-------------+---------------------+---------------------+------+
8 rows in set (0.00 sec)

mysql>