欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-14 10:20:13
[TOC] 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 ......

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