MySQL 裡面，數字與字串做是否相等的判斷

 

如果 2308 與 '2308'，一個是數字，一個是字串，這兩個相比較，是否相等？

測試結果是：在mysql 裡面，是相等的。

ER-Model identifying relationships 與 non-identifying relationships的差別

https://www.geeksforgeeks.org/difference-between-identifying-and-non-identifying-relationships/

https://matthung0807.blogspot.com/2018/03/er-model-identifying-relationships-non.html

結論：簡單說：

實的關聯 (identifying relationships)：一個是母表 vs 子表，外部關聯鍵 foreign key的值，是不能為NULL，一定要有值！

虛的關聯 (non-identifying relationships)：foreign key 可以為 NULL

MySQL bit(1) 邏輯欄位 配合 View 後，竟然變了！

在資料庫表單中，經常有 Yes/No 的欄位，例如：是否已經繳款？是否審核通過？等。

原本，按理說，這樣的邏輯是否資料值的欄位，只要用 bit(1) 就足夠了。所以，我原本也就是用 bit(1) 來記錄就可以了。

結果在昨天設計SC時，由於需要使用到 VIEW，也就是SC是透過VIEW的方式來建立 GRID，(這另外說)，結果 bit(1) 欄位值在 VIEW 的後面，都變成 True ！連 False 也變成 True！造成程式跑出來當然就錯了！這不算是 SC的坑，但是 MySQL + VIEW 的坑了！我只好將 bit(1)，改成 tinyint(1) 來記錄 0/1，這樣修改資料表結構以後，程式就完全正常了！

在這裡紀錄一下，一個小坑！

要記得 SQL 如果有 SUM等函數，要加上 IFNULL(sum(num_field), 0)

要記得 SQL 如果有 SUM等函數，要加上 IFNULL(sum(num_field), 0) 

否則，就會產生 NULL 的可能，導致後面如果有再拿資料處理的時候，就會發生錯誤。

記得喔！

SELECT IFNULL(sum(c_profit),0) as v_sum_profit, count(*) as v_count, IFNULL(sum(c_profit)/count(*),0) as v_avg 

FROM max_orders 

WHERE date(updated_at_in_ms)='2023-07-08' 

and state='done' 

and side='sell';

MySQL utf8 / utf8mb4?

MySQL資料庫，現在新的資料庫，最好都直接用 utf8mb4 了！

collate 用 utf8mb4_unicode_ci

當建立一個新的專案的時候，要先留意把這個資料庫的字元設定好，全部一致，這樣可以減少很多後面的一大堆麻煩！

MySQL IF EXIST UPDATE ELSE INSERT

 以下提供的是一般在大量倒資料時，會用到的，請僅慎使用！！

================================================================

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue') THEN

    UPDATE Table1 SET (...) WHERE Column1='SomeValue'

ELSE

    INSERT INTO Table1 VALUES (...)

END IF;

================================================================

UPDATE Table1 SET (...) WHERE Column1='SomeValue'

IF @@ROWCOUNT=0

    INSERT INTO Table1 VALUES (...)

MySQL sql_mode=only_full_group_by

MySQL 產生 sql_mode=only_full_group_by

這是我做一個 View 裡面有 GROUP BY

在我的 Local MySQL沒有問題，上傳到  Server 就有問題了。

這一定就是版本不同的緣故。

於是網路搜尋：

https://www.gushiciku.cn/pl/pFxA/zh-tw

https://www.twle.cn/c/yufei/mysqlfav/mysqlfav-basic-sql_mode2.html

修改了 Server 上面 /etc/my.cnf

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

加上這段，重新啟動MySQL 就好了！

MYSQL: IF NOT EXIST () THEN ..... END IF;

 IF not exists (SELECT * FROM funit WHERE fu_id = in_myfu_id) THEN

ROLLBACK;

SET out_ret_code = CONCAT("ERROR: 操作人經營單位不存在資料庫中!",in_myfu_id);

CALL system_log(out_ret_code);

LEAVE proc_label;

END IF;

MySQL ERROR 1292 (22007): Truncated incorrect DOUBLE value

ERROR 1292 (22007): Truncated incorrect DOUBLE value

這個 Warning產生了。

經查是我這個個案是資料型態不一致造成的。

程式裡面，做了一個變數

DECLARE v_variable VARCHAR(100);

SET v_variable = IFNULL((SELECT abc FROM table WHERE id = 'aaa'),0);

IF (v_variable = 0) THEN

.........................

ELSE

...................

END IF;

v_variable 資料型態是文字，將他設為數字 0！

後面又拿文字來跟數值比較！所以產生了這個警告！

修改一下，就好了。

DECLARE v_variable VARCHAR(100);

SET v_variable = (SELECT abc FROM table WHERE id = 'aaa');

IF (v_variable IS NULL) THEN

.........................

ELSE

...................

END IF;

MySQL 資料庫 + Stored Procedure 設計通則

一、資料庫部分

二、程式部分

一、資料庫部分

使用英文名稱

Comment 盡量不要使用

二、程式部分

proc_label: BEGIN

/*

-- Program name:  program_name.sql

-- INPUT 參數說明：

-- IN in_gamer_id .....

-- IN in_gamer_id .....

-- IN in_gamer_id .....

-- 程式說明：

-- 作者：

-- 日期：

OUT `out_ret_code` varchar(100) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci

*/

DECLARE v_program_name VARCHAR(100) DEFAULT "porgram_name.sql";

-- 這個變數是為了方便輸出訊息時，便於顯示程式名稱，方便偵錯。

DECLARE EXIT handler for sqlexception

BEGIN

  GET DIAGNOSTICS CONDITION 1
  @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
  ROLLBACK;
  SET out_ret_code =(SELECT concat("RETURNED_SQLSTATE：",@p1 ," MESSAGE_TEXT：" ,@p2 ));
  CALL system_log(CONCAT("sqlexception:",out_ret_code));
END;

DECLARE EXIT handler for sqlwarning

BEGIN

  GET DIAGNOSTICS CONDITION 1

  @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;

  ROLLBACK;

  SET out_ret_code =(SELECT concat("RETURNED_SQLSTATE：",@p1 ," MESSAGE_TEXT：" ,@p2 ));

  CALL system_log(CONCAT("sqlwarning:",out_ret_code));

END;

-- 判斷傳入參數是否正確

IF input_is_error() THEN
SET out_ret_code = CONCAT("ERROR: 幫別人註冊: CP not enough!",v_my_cp, " bunit_type:",in_bunit_type);
CALL system_log(out_ret_code);
LEAVE proc_label;

END IF;

/*

先處理參數正確性檢查
先處理不用寫資料庫的運算
最後要寫資料庫時，再
START TRANSACTION
COMMIT;
ROLLBACK;*/

START TRANSACTION;

/*
資料庫更新動作
*/

SET out_ret_code = "SUCCESS";

COMMIT;

END;

IF NOT EXISTS(SELECT * FROM People) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OMG PANIC';
END IF;

MySQL Stored Procedure Cursor 的標準程式模板

DECLARE v_exit INT DEFAULT FALSE;
DECLARE cur_table CURSOR FOR
  SELECT  field1, field2 FROM table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit = TRUE;
 
OPEN cur_table;
my_fetch: LOOP
 FETCH cur_table
 INTO v_field1, v_field2;
 IF v_exit THEN
   LEAVE my_fetch;
 END IF;
<process code here...>

END LOOP my_fetch;
 
CLOSE cur_table;

** PHP 程式部分，要配合的程式碼：

EVENT_BUTTON_UPDATE_ONCLICK()

// --- Custom Code

    $db = new clsDBconn_mysql();

    $sql1 = "CALL add_new_proc(".

$db->ToSQL($_SESSION["ID"],ccsText) . ",".

$control->ctrl->GetValue()  . "," .

$db->ToSQL($aaaaa,ccsText). "," .

"@ret_code)";

    $db->query($sql1);

        

    $sql_log = "CALL system_log(".$db->ToSQL($sql1, ccsText).")";

    $db->query($sql_log);

    $sql2 = "SELECT @ret_code";

    $db->query($sql2);

    $Result = $db->next_record();

    global $Redirect;

    $Redirect = "success.php?ret_code=".$db->f("@ret_code");

    $db->close();

 

php 使用 SQL 時，引號要避開，否則會出錯！mysqli_real_escape_string()

 

<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");

if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}

// Escape special characters, if any
$firstname = $mysqli -> real_escape_string($_POST['firstname']);
$lastname = $mysqli -> real_escape_string($_POST['lastname']);
$age = $mysqli -> real_escape_string($_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$firstname', '$lastname', '$age')";

if (!$mysqli -> query($sql)) {
  printf("%d Row inserted.\n", $mysqli->affected_rows);
}

$mysqli -> close();
?>

像下面這個例子： $lastname 裡面的值有單引號。這樣進入 SQL 就會發生錯誤，應該要避開。

就使用這個函數：mysqli_real_escape_string()，就可以把單引號等避開了！

<?php

$lastname = "D'Ore";

$sql="INSERT INTO Persons (LastName) VALUES ('$lastname')";

// This query will fail, cause we didn't escape $lastname
if (!$mysqli -> query($sql)) {
  printf("%d Row inserted.\n", $mysqli->affected_rows);
}

?>

https://www.w3schools.com/php/func_mysqli_real_escape_string.asp

https://www.php.net/manual/en/mysqli.real-escape-string.php

關於商業規則 Business Rules

每個企業，都有他的企業商業規則，Business Rules。

這些商業規則，應該要獨立的存放在 MySQL資料庫中。

設計師應該要記住：商業規則不應該放在應用程式中。

商業規則，可以被應用程式使用，但不是把他寫死在營用城市裡面。

原因：

1- 商業規則是不斷發展中的

2- 情況是會改變的，例如有了新的規則

3-這個商業規則可能會使用在不同的應用程式裡面。

MySQL 和 商業規則

負責商業規則的程式設計師，要建立一個兩層的應用程式：

1 - 商業規則層

2 - 應用程式層

使用 MySQL 資料庫系統就是一個建立商業規則層的明顯選擇。

就是因為他可以使用大部分的程式語言，而且程式設計師可以：

1 - 將商業規則資訊存放在 資料表 Table中

2 - 使用 stored function/procedure 來執行商業規則。

使用 MySQL 建立好的商業規則，可以很便利的為使用各種不同的程式語言寫得贏用程式來呼叫使用。包括在網路上的應用程式。

建立簡單的 MySQL Stored Function 商業規則

商業規則本身，可以使用 MySQL的函數來建立。

很明顯的，這些規則可以使用 if.. then..else 指令來完成。

例如：根據輸入的項目，而採取不同的行動，程式：

delimiter //
drop function if exists business_rule;
create function business_rule (status varchar(50)) returns varchar(50)
deterministic
begin
declare new_status varchar(50);
if status = "new" then
set new_status = "case opened - send to engineer";
elseif status = "fixed" then
set new_status = "problem solved - inform customer";
elseif status = "nofix" then
set new_status = "problem unresolved - sent to engineer";
elseif status = "raise" then
set new_status = "problem escalated - inform manager";
elseif status = "close" then
set new_status = "case closed";
else set status = "undefined";
end if;
return new_status;
end
//
delimiter ;

這個函數，可以被應用程式呼叫使用：

echo "select business_rule('new')" | mysql -uuser -ppassword business_rules_db

這樣一來，

1. 這些商業規則，可以被不同語言的應用程式使用，或任何可以使用MySQL的應用程式

2.  商業規則可以被獨立更新或修改、增加而不需要更動應用程式。

最後，程式設計師可以有一個有力且可適應修改商業規則，而與使用他們的應用程式獨立運作。

簡單翻譯自：

https://steemit.com/utopian-io/@haig/how-to-use-business-rules-in-a-mysql-database-using-mysql-functions-to-create-two-tier-business-savvy-applications

mysql unique index null 不計

 mysql 裡面，unique index key如果含有 null，他並未計入唯一的檢查。還是可以接受兩筆以上的 null資料。

如果希望不能兩筆null資料，那就只好將 null 資料，改為 ""、空字串，這樣就可以辨識唯一了。

CREATE TABLE table1 (x INT NULL UNIQUE);
INSERT table1 VALUES (1);
INSERT table1 VALUES (1);   -- Duplicate entry '1' for key 'x'
INSERT table1 VALUES (NULL);
INSERT table1 VALUES (NULL);
SELECT * FROM table1;

Insert Into Table(A, B) Values (null, 2);
Insert Into Table(A, B) Values (null, 2);#should fail do to duplicate values

這種情形，mysql 還是允許。

若改成""，就可以由Index來限制唯一了！

Insert Into Table(A, B) Values ("", 2);
Insert Into Table(A, B) Values ("", 2);#should fail do to duplicate values

記得：這樣一來，使用程式在做判斷時，就要使用  empty()來判斷

MySQL Stored Procedure Cursor 的標準程式模板

DECLARE v_exit INT DEFAULT FALSE;

DECLARE cur_table CURSOR FOR
SELECT  field1, field2 FROM table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit = TRUE;


OPEN cur_table;

my_fetch: LOOP
FETCH cur_table
INTO v_field1, v_field2;
IF v_exit THEN
LEAVE my_fetch;
END IF;

<process code here...>


END LOOP my_fetch;

CLOSE cur_table;

Change mysql root password on Centos7

https://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7

1. Stop mysql:
systemctl stop mysqld

2. Set the mySQL environment option 
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. Start mysql usig the options you just set
systemctl start mysqld

4. Login as root
mysql -u root

5. Update the root user password with these mysql commands
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
    -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

*** Edit ***
As mentioned my shokulei in the comments, for 5.7.6 and later, you should use 
   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Or you'll get a warning

6. Stop mysql
systemctl stop mysqld

7. Unset the mySQL envitroment option so it starts normally next time
systemctl unset-environment MYSQLD_OPTS

8. Start mysql normally:
systemctl start mysqld

Try to login using your new password:
7. mysql -u root -p

mysql: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=

以下整理一下結論：

1. 所有的都改成 utf8mb4/utf8_unicode_ci

產生不一致的情形時，就是 Store Procedure/Trigger 裡面的變數、參數

使用時，都再指定為 utf8mb4_unicode_ci

https://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic

資料寫說，不要用 general_ci了

https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci/766996#766996

以下是網友的網頁，留下來參考

https://www.cnblogs.com/yjmyzz/p/5088109.html

mysql: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '= 的解决

昨天把mysql里所有table的varchar字段的字符集，批量换成了utf8mb4/utf8mb4_unicode_ci ，以便能保存一些emoji火星文 , 结果有一个sql语句执行时，报错如下：

Illegalmixofcollations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)foroperation '=

观察了一下，这个sql使用了一个自定义的function，这个函数的入口参数为varchar，类似如下：

CREATE FUNCTION `f_xxx`(
  p_ref_type     VARCHAR(50) ,
  ...
)

问题就在这里，把p_ref_type的参数类型改成：

CREATE FUNCTION `f_xxx`(
  p_ref_type     VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  ...
)

就ok了。

当然，如果不想改function，还有一种一劳永逸的解决办法：（需要停机）

出错信息的意思是 utf8-unicode-cli与utf-general-cli 不能混用，可以用

show create function f_xxx\G 查看下结构，重点观察下最后几行：

...

  END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

这里可以看出，collation_connnection 与 Database Collation所用的字符集不一致，要想办法弄成一致，可以再用：

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

查看mysql的相关系统变量：

| 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_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |

大概输出是这样的，注意最后3行，确认mysql实例的编码后，可以调整database的编码：

ALTER DATABASE `数据库名` character set utf8mb4 COLLATE utf8mb4_general_ci ;

考虑到，有些表或字段在创建时，可能也指定了其它编码，可以用:

./mysqldump -u用户名 -p -d --add-drop-table 数据库名 > 导出的文件名

注：为防止操作期间有人操作数据库，最好将所有应用暂时停机，保证操作期间db没人向其写入数据 。

导出表结构的sql，然后用文本编码工具，批量把指定字符集的字符串给批量清空，即：清除所有指定字符集的DDL语句，全部默认用database本身的字符集

接下来用

./mysqldump -u用户名 -p -t 数据库名 > 导出的文件名 

导出所有数据

最后，干掉所有table，然后mysql命令行下

source 修改好以后的数据库表结构.sql

source 数据.sql

将所有表重建，然后导入数据，进行恢复，最终目标是将整个db,包括所有表及字段的字符集大换血了