Django系列---使用MySql数据库

目录

• • 1.3. 为django项目新建一个数据库用户
• 2. 修改django的配置

django默认使用的sqlite3，这在实际的生产环境中是不推荐的；

1. 创建数据库

linux vm_0_15_centos 3.10.0-693.el7.x86_64 #1 smp tue aug 22 21:09:27 utc 2017 x86_64 x86_64 x86_64 gnu/linux

1.1. 使用utf8mb4编码

mysql的utf-8编码最多只支持3个字节，而移动端的一些表情都是以4个字节存储的；utf8mb4是一个替代的方案，建议创建数据库和表都以utf8mb4替代utf-8

1.1.1. 确定mysql的配置文件

# 系统中my.cnf文件的位置
[luizyao@vm_0_15_centos ~]$ locate my.cnf
/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/server.cnf

# mysql启动时，读取配置文件的目录顺序
[luizyao@vm_0_15_centos ~]$ mysql --help | grep 'my.cnf'
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
                      order of preference, my.cnf, $mysql_tcp_port,

1.1.2. 修改配置文件

/etc/my.cnf

[client] 
default-character-set = utf8mb4

[mysql] 
default-character-set = utf8mb4

[mysqld]
# settings user and group are ignored when systemd is used.
# if you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/systemd
character-set-client-handshake = false 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='set names utf8mb4'

1.1.3. 重启数据库服务，检查相关字段

# 保证character_set_client、character_set_connection、character_set_database、character_set_results和character_set_server的值一定是utf8mb4
mariadb [(none)]> show variables where variable_name like 'character_set_%' or variable_name like 'collation%';
+--------------------------+----------------------------+
| variable_name            | value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+
11 rows in set (0.02 sec)

1.1.4. 新建数据库

mariadb [(none)]> create database blogproject;
query ok, 1 row affected (0.01 sec)

--查看blogproject创建时候使用的编码，回显中注释的部分可以看出，使用的是utf8mb4编码
mariadb [mysql]> show create database blogproject;
+-------------+----------------------------------------------------------------------------------------------------+
| database    | create database                                                                                    |
+-------------+----------------------------------------------------------------------------------------------------+
| blogproject | create database `blogproject` /*!40100 default character set utf8mb4 collate utf8mb4_unicode_ci */ |
+-------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.2. 使用已经存在的数据库

1.2.1. 修改已有数据库的编码

mariadb [(none)]> alter database blogproject character set utf8mb4;

1.3. 为django项目新建一个数据库用户

-- 赋予这个新用户增删改查等权限，不授予drop的权限；并且，只允许本地客户端登陆；
mariadb [mysql]> grant alter,create,delete,index,insert,select,update,trigger on blogproject.* to <用户名>@localhost identified by '<密码>'; 
query ok, 0 rows affected (0.04 sec)

mariadb [mysql]> flush privileges;
query ok, 0 rows affected (0.03 sec)

-- 检查权限，秘密默认是加密存储
mariadb [blogproject]> show grants for <用户名>@localhost;
+----------------------------------------------------------------------------------------------------------------+
| grants for <用户名>@localhost                                                                                    |
+----------------------------------------------------------------------------------------------------------------+
| grant usage on *.* to '<用户名>'@'localhost' identified by password '*5102144ca406fc026831d796ea07645447677551'  |
| grant select, insert, update, delete, create, index, alter, trigger on `blogproject`.* to '<用户名>'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2. 修改django的配置

2.1. 修改settings.py中数据库相关

databases = {
    # 'default': {
    #     'engine': 'django.db.backends.sqlite3',
    #     'name': os.path.join(base_dir, 'db.sqlite3'),
    # }
    'default': {
        'engine': 'django.db.backends.mysql',
        'name': 'blogproject',        
        'user': '<用户名>',
        'password': '<用户名>',
        'host': '<数据库服务器的ip>',
        'port': '3306',  # 默认的服务端口号
        'options': {
            # 存储引擎启用严格模式，非法数据值被拒绝
            'init_command': "set sql_mode='strict_trans_tables'",  
            'charset': 'utf8mb4',
        },
    }
}

2.2. 安装mysqlclient

darwin luizyaodemacbook-air.local 18.6.0 darwin kernel version 18.6.0: thu apr 25 23:16:27 pdt 2019; root:xnu-4903.261.4~2/release_x86_64 x86_64

2.2.1. 安装mysql-connector-c

luizyaodemacbook-air:~ luizyao$ brew install mysql-connector-c
==> downloading https://mirrors.ustc.edu.cn/homebrew-bottles/bottles/mysql-conne
######################################################################## 100.0%
==> pouring mysql-connector-c-6.1.11.mojave.bottle.tar.gz