QA · 數據庫學習摘記

1、[Err] 1442 - Can't update table 't_teacher' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. You cannot change a table while the INSERT trigger is firing. The INSERT might do some locking which could result in a deadlock. Also, updating the table from a trigger would then cause the same trigger to fire again in an infinite recursive loop. Both of these reasons are why MySQL prevents you from doing this. However, depending on what you're trying to achieve, you can access the new values by using NEW.fieldname or even the old values--if doing an UPDATE--with OLD. If you had a row named full_brand_name and you wanted to use the first two letters as a short name in the field small_name you could use: CREATE TRIGGER `capital` BEFORE INSERT ON `brandnames` FOR EACH ROW BEGIN SET NEW.short_name = CONCAT(UCASE(LEFT(NEW.full_name,1)) , LCASE(SUBSTRING(NEW.full_name,2))) END 2、報錯:1130-host ... is not allowed to connect to this MySql server 開放mysql遠程連接不使用localhost 報錯:1130-host ... is not allowed to connect to this MySql server 解決方法： 1. 改表法。可能是你的帳號不允許從遠程登陸，只能在localhost。這個時候只要在localhost的那臺電腦，登入mysql后，更改 "mysql" 數據庫里的 "user" 表里的 "host" 項，從"localhost"改稱"%" mysql -u root -pvmwaremysql>use mysql; mysql>update user set host = '%' where user = 'root'; mysql>select host, user from user; 2. 授權法。例如，你想myuser使用mypassword從任何主機連接到mysql服務器的話。 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; FLUSH PRIVILEGES; 如果你想允許用戶myuser從ip為192.168.1.6的主機連接到mysql服務器，并使用mypassword作為密碼 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; FLUSH PRIVILEGES; 如果你想允許用戶myuser從ip為192.168.1.6的主機連接到mysql服務器的dk數據庫，并使用mypassword作為密碼 GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; FLUSH PRIVILEGES; 我用的第一個方法,剛開始發現不行,在網上查了一下,少執行一個語句 mysql>FLUSH RIVILEGES 使修改生效.就可以了另外一種方法,不過我沒有親自試過的,在csdn.net上找的,可以看一下. 在安裝mysql的機器上運行： 1）d:\mysql\bin\>mysql -h localhost -u root //這樣應該可以進入MySQL服務器 2）mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION //賦予任何主機訪問數據的權限 3）mysql>FLUSH PRIVILEGES //修改生效 4）mysql>EXIT //退出MySQL服務器這樣就可以在其它任何的主機上以root身份登錄啦！