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

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 | head -n 7

mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Usage: mysql [OPTIONS] [database]

Default options are read from the following files in the given order:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

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