CentOS 7 中yum安装MariaDB
官方安装说明地址:https://mariadb.com/kb/en/library/mariadb-package-repository-setup-and-usage/
1. 设置仓库
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
2.执行安装
yum install MariaDB-server
3.启动服务
systemctl start mysql.service
4.服务随系统启动
systemctl enable mysql.service
5.取消随系统启动
systemctl disable mysql.service
6.修改root密码和远程访问权限
命令行输入mysql,进到数据库命令提示行,use mysql,
grant all privileges on *.* to root@"%" identified by "mima";
flush privileges;
'all privileges ':所有权限 也可以写成 select ,update等。
. 所有库的所有表 如 databasename.*。
% 数据库所在的IP, %表示所有。
identified by ‘密码’ 表示通过密码连接
##MariaDB字符集编码
查看数据库支持的所有字符集
SHOW CHARACTER SET;
SHOW COLLATION;
一.查看编码
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
默认是latin1,外部访问数据乱码的问题就出在这个connection连接层上
二.设置编码
命令行修改
-
SET NAMES ‘utf8’;
它相当于下面的三句指令:
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
一般只有在访问之前执行这个代码就解决问题了 -
创建数据库是指定编码
CREATE DATABASE database_name CHARACTER SET utf8;
MariaDB [(none)]> CREATE DATABASE foo CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE foo;
Database changed
MariaDB [foo]> 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/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.00 sec)
MariaDB [foo]> SHOW VARIABLES LIKE ‘collation%’;
±---------------------±------------------+
| Variable_name | Value |
±---------------------±------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
±---------------------±------------------+
3 rows in set (0.00 sec)
注意:虽然在建库的时候有指定字符集,但character_set_server 仍然是latin1, 需要修改配置文件/etc/my.cnf
- 创建表指定编码
CREATE TABLEtype
(
id
int(10) unsigned NOT NULL auto_increment,
flag_deleted
enum(‘Y’,‘N’) character set utf8 NOT NULL default ‘N’,
flag_type
int(5) NOT NULL default ‘0’,
type_name
varchar(50) character set utf8 NOT NULL default ‘’,
PRIMARY KEY (id
)
) DEFAULT CHARSET=utf8;
示例:
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
CREATE OR REPLACE TABLE mytable
(columnn1 INT,
columnn2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;
还可以限制某个字段的字符集作为筛选条件
-
修改数据库成utf8
ALTER DATABASE database_name CHARACTER SET utf8; -
修改表默认用utf8
ALTER TABLE table_name CHARACTER SET utf8; -
修改字段用utf8
MariaDB [foo]> DESC hello;
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| id | int(10) | YES | | NULL | |
| name | char(50) | YES | | NULL | |
±------±---------±-----±----±--------±------+
2 rows in set (0.00 sec)
MariaDB [foo]> ALTER TABLE hello MODIFY name char(50) CHARACTER SET utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
配置文件
Linux下一般是 /etc/my.cnf,Windows下一般在系统目录下或者在MySQL的安装目录下名字叫my.ini
[mysqld]
character_set_server = utf8
lower_case_table_names = 1 //表名不区分大小写(此与编码无关)
[mysqld_safe]
character_set_server = utf8
[mysql]
default-character-set = utf8
[mysql.server]
default-character-set = utf8
[client]
default-character-set = utf8
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.41-MariaDB MariaDB Server
Copyright © 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> SHOW VARIABLES LIKE ‘collation%’;
±---------------------±----------------+
| Variable_name | Value |
±---------------------±----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
±---------------------±----------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> 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 | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.00 sec)
注意:新版本的mariadb,如mariadb-10.1的配置些许有些变化
[mysqld]
init_connect = ‘SET collation_connection = utf8_general_ci’
init_connect = ‘SET NAMES utf8’
character_set_server = utf8
collation_server = utf8_general_ci
[mysqld_safe]
init_connect = ‘SET collation_connection = utf8_general_ci’
init_connect = ‘SET NAMES utf8’
character_set_server = utf8
collation_server = utf8_general_ci