iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 14
0

PHP的基礎語法教學已經在上回告一段落了,接下來要進入連接資料庫的部份。
不過在這之前,要先熟悉資料庫的使用方法。
所以今天要來教大家如何透過基本語法,將資料存到資料庫裡。

打開terminal,輸入:

$mysql -uroot -p

接著輸入mySQL的密碼,就會看到這個畫面:
https://ithelp.ithome.com.tw/upload/images/20190929/20120024QzqC0xKw35.png
首先新增一個 MySQL 使用者

GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

把 username, password 換成要新增的username和密碼即可. (密碼必須符合你所設定的密碼規則)

說明:
ALL: 授權的權限(SELECT, INSERT .... etc)
.: Table Name, Db_name.*, 指定可以存取哪些Db/Table
username: 要新增的 username
localhost: 可從哪邊來存取, 可用 % (% 代表全部)

接下來我們要先建立一個database(資料庫),語法如下:

create database 資料庫名稱;

MySQL語法和PHP的陳述句一樣,每句結尾都要加上';'表示結束。

看到這行就表示建立成功:

Query OK, 1 row affected (0.01 sec)

然後,我們要在這個database中建立tables(資料表):

use 資料庫名稱;

建立一個table
格式如下:

create table 資料表名稱 (欄位1名稱 資料型態(資料長度), 欄位2名稱 資料型態(資料長度), 欄位3名稱 資料型態(資料長度), 欄位4名稱 資料型態(資料長度));

例如:

create table students(id integer auto_increment primary key, name char(20), score integer(3));

常用資料庫資料型態

  1. integer (整數)
  2. char (1~255字元字串)
  3. varchar (不超過255字元不定長度字串)
  4. text (不定長度字串最多65535字元)

auto_increment為自動增加,每次新增一筆資料進去,使用者不必自己輸入這欄,MySQL會自動幫你新增。

primary key則是table中具有唯一性與識別性的欄位,每個table中都只能有一個欄位是primary key。

儲存一筆資料進入table,格式如下:

insert into 資料表名稱(欄位1,欄位2,欄位3,欄位4)
values('值1','值2','值3','值4');

假設我們要新增四筆學生的資料:

insert into students(name, score) values('Rose', 87);
insert into students(name, score) values('Lisa', 88);
insert into students(name, score) values('Jisoo', 89);
insert into students(name, score) values('Jenny', 90);

由於id是auto_increment的欄位,因此不需要另外輸入。

接著我們想看看所有學生的資料,查看table所有欄位的格式如下:

select*from students;

就可以看到這個畫面了:

mysql> select* from students;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | Rose  |    87 |
|  2 | Lisa  |    88 |
|  3 | Jisoo |    89 |
|  4 | Jenny |    90 |
+----+-------+-------+

若想要修改其中一筆資料的內容,則格式如下:

update 資料表名稱 set 欄位1='值1',欄位2='值2',欄位3='值3',... 欄位N='值N'
where (欄位='值');

例如:

update students set score=86 where (name='Jenny');

再次查看table所有欄位,就能看到更新過的內容了

mysql> select* from students;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | Rose  |    87 |
|  2 | Lisa  |    88 |
|  3 | Jisoo |    89 |
|  4 | Jenny |    86 |
+----+-------+-------+

刪除一筆資料:

delete from 資料表名稱 where(id='4');

有中文的部份出現亂碼怎麼辦?

MySQL 亂碼解決方法:

show variables like 'collation%';
show variables like 'char%'; 查看設定狀態
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

若不是utf-8,vim /etc/mysql/my.cnf (或/etc/my.cnf) 在最下面加上

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

權限不足的話,:w !sudo tee %
然後terminal輸入:

$service mysql restart

(mariadb則是)

$service mariadb restart

db跟table、columns也要設定為utf8

show create database databaseName; = SHOW CREATE SCHEMA databaseName;

查看資料表建表資訊(包含編碼)

show create table tableName;

因為character_set_database是latin1,因此table建立時預設也是latin1

+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| tests | CREATE TABLE `tests` (
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------+

查看資料表欄位屬性

show full columns from tableName;
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type     | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| name  | char(20) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+----------+-------------------+------+-----+---------+-------+---------------------------------+---------+
SELECT default_character_set_name FROM information_schema.SCHEMATA 
WHERE schema_name = "databaseName";
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "databaseName" AND T.table_name = "tableName";
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "board" AND T.table_name = "databaseName";
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation   AND T.table_schema = "schemaname";

另外也可以將mysqli_set_charset($db,"utf8"); 放在php script 裡試試看(但我還不確定有沒有作用)。

把之前有輸入過中文且原本呈現亂碼的資料清空,再輸入新的資料應該就能看到中文了

mysql預設編碼規則

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
  • 在server層級編碼為utf8下的情況create database,則character_set_database就會是utf8
  • 以此類推,在databse層級編碼為utf8下的情況下create table,則character_set_table也會是utf8,column同理。
  • 但已建立的database、table等等不會因為上一階層的編碼修改而跟著改動

2021/4/15補充:
建議儘量用utf8mb4,如此一來儲存emoji的時候才不會爆炸。
但要注意的是,utf8是把3個byte當一個字元,utf8mb4則是把一個字元當4個byte,
以innodb作為資料庫引擎來說,字串長度的最大限制為767個字元;myisam則是1000 byte。
因此當編碼為utf84,varchar長度預設為255時,244*4 > 767,在innodb就會報錯
Specified key was too long; max key length is 767 bytes

這時有幾種做法可以解決這個問題:

  1. 指定varchar, index等開出的最大長度為191 (191*4 < 767)
  2. 將資料庫引擎改為myisam (改完要重新啟動 mysql)
  3. 在InnoDB下調整innodb_large_prefix參數,來使得字串長度的長度突破767。
    不過有幾件事注意:
    ①調整innodb_large_prefix參數只能取消對索引中每列長度的限制,但是無法取消對索引總長度的限制
    ②使用innodb_large_prefix來突破長度限制時必須同時指定innodb_file_format=barracuda,innodb_file_per_table=true,且建表的時指定表的row_format為dynamic或者compressed(mysql 5.6中row_format預設值為compact)

上一篇
Day 13 淺談物件導向(三):來聊聊封裝與繼承
下一篇
Day 15 PHP連接資料庫
系列文
後端基礎PHP+Mysql & Laravel 30日養成計畫36
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言