mysql日常使用问题汇总
2018-04-22 本文已影响17人
nextliving
在使用mysql的过程中会遇到各种各样的小问题，本文主要记录下这些问题及我自己的实践经验,本文会不定时更新。
No1.修改已创建表的某个字段为auto_increment
我之前创建了一个很简单的用户表user:
create table user (
id int not null primary key,
username varchar(20),
age int
);
可以使用命令
describe user
查看表的详情：
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
现在需要修改id,让id能够自动增长，只需执行:
alter table user change id id int auto_increment
，如果之前没把id设为主键，需要先设置id为主键再执行上面的修改操作，只是因为一个表中只能有一个字段是auto_increment且该字段为主键。
No.1参考
No.2查看正在使用哪个数据库
一是使用
mysql> status;
查看当前正在使用哪个数据库及其状态:
--------------
mysql Ver 14.14 Distrib 5.7.16, for osx10.11 (x86_64) using EditLine wrapper
Connection id: 4
Current database: elec
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.16 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 12 days 2 hours 19 min 45 sec
Threads: 1 Questions: 34 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.000
--------------
二是使用
mysql> select database();
查看当前正在使用的数据库:
+------------+
| database() |
+------------+
| elec |
+------------+
1 row in set (0.00 sec)
No.2参考
No.3指定创建的数据库默认字符集编码为UTF8
假设数据库名为iengchen,则创建语句为:
mysql> create database iengchen default character set utf8;
No.3参考
No.4查看数据库默认字符编码
mysql> SHOW VARIABLES LIKE 'character%';
终端输出
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.16-osx10.11-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)
No.5查看某个表中所有字段的相关信息
mysql> show full columns from Elec_Text;
然后就可以看到表Elec_Text中全部的字段信息了:
+------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| textID | varchar(50) | utf8_general_ci | NO | PRI | NULL | | select,insert,update,references | |
| textName | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| textDate | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| textRemark | varchar(500) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.01 sec)
