åè¨
å¨å¼åè¿ç¨ä¸æ们ä¸ç®¡æ¯ç¨æ¥æµè¯æ§è½è¿æ¯å¨ç产ç¯å¢ä¸é¡µé¢å±ç¤ºå¥½çä¸ç¹, åæè å¦ä¹ éªè¯æä¸ç¥è¯ç¹ç»å¸¸éè¦ä¸äºæµè¯æ°æ®, è¿ä¸ªæ¶åå¦æææ²çè¯, åè¡äºåè¡è¿å¥½, å¤äºå°±å¾æ»äº¡äº, æ¥ä¸æ¥ä»ç»ä¸¤ç§å¸¸ç¨çMySQLæµè¯æ°æ®æ¹éçææ¹å¼
- åå¨æ¹å¼+å½æ°
- Navicatçæ°æ®çæ
表
åå¤äºä¸¤å¼ 表 è§è²è¡¨:
- id: èªå¢é¿
- role_name: éæºå符串, ä¸å 许éå¤
- orders: 1-1000ä»»ææ°å
ç¨æ·è¡¨:
- id: èªå¢é¿
- username: éæºå符串, ä¸å 许éå¤
- password: éæºå符串, å 许éå¤
- role_id: 1-10wä¹é´çä»»ææ°å
建表è¯å¥:
sqlå¤å¶ä»£ç CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT 'ç¨æ·å',
`role_id` int(11) DEFAULT NULL COMMENT 'è§è²id',
`password` varchar(255) DEFAULT NULL COMMENT 'å¯ç ',
`salt` varchar(255) DEFAULT NULL COMMENT 'ç',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(255) DEFAULT NULL COMMENT 'è§è²å',
`orders` int(11) DEFAULT NULL COMMENT 'æåºæé\r\n',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
使ç¨å½æ°çæ
éè¿åå¨è¿ç¨å¿«éæå ¥, éè¿å½æ°ä¿è¯æ°æ®ä¸éå¤
设置å 许å建å½æ°
æ¥ç MySQLæ¯å¦å 许å建å½æ°
sqlå¤å¶ä»£ç SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
ç»æå¦å¾æ示, æ们使ç¨ä»¥ä¸å½ä»¤å°å建å½æ°åè½æå¼ï¼global-ææsessioné½çæï¼
sqlå¤å¶ä»£ç SET GLOBAL log_bin_trust_function_creators=1;
è¿ä¸ªæ¶ååä¸æ¬¡æ¥è¯¢å°±ä¼æ¾ç¤ºå·²æå¼
产çéæºå符串
sqlå¤å¶ä»£ç -- éæºäº§çå符串
DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $
-- åå¦è¦å é¤
-- drop function rand_string;
产çéæºæ°å
sqlå¤å¶ä»£ç -- ç¨äºéæºäº§çåºé´æ°å
DELIMITER $
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;
END$
-- åå¦è¦å é¤
-- drop function rand_num;
å建åå¨è¿ç¨
æå ¥è§è²è¡¨
sqlå¤å¶ä»£ç -- æå
¥è§è²æ°æ®
DELIMITER $
CREATE PROCEDURE insert_role(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO role ( role_name,orders ) VALUES (rand_string(8),rand_num(1,5000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$
-- å é¤
-- DELIMITER ;
-- drop PROCEDURE insert_role;
æå ¥ç¨æ·è¡¨
sqlå¤å¶ä»£ç -- æå
¥ç¨æ·æ°æ®
DELIMITER $
CREATE PROCEDURE insert_user(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO user (username, role_id, password, salt ) VALUES (rand_string(8) ,rand_num(1,100000), rand_string(10), rand_string(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$
-- å é¤
-- DELIMITER ;
-- drop PROCEDURE insert_user;
æ§è¡åå¨è¿ç¨
sqlå¤å¶ä»£ç -- æ§è¡åå¨è¿ç¨ï¼å¾dept表添å 10ä¸æ¡æ°æ®
CALL insert_role(100000);
-- æ§è¡åå¨è¿ç¨ï¼å¾emp表添å 100ä¸æ¡æ°æ®ï¼ç¼å·ä»100000å¼å§
CALL insert_user(100000,1100000);
æ»ç»
æ§è¡ç¨æ¶ 10wæ°æ®å·®ä¸å¤ååé, 100wæ°æ®è¶ è¿äº20åé, åæ¶ userçåå¨è¿å¡æ»å¾ä¹ ...
æåé½æåæ°å¢, ä½æ¯èªå¨éå¢å¼åè¡æ°ä¸ä¸è´, è¿ä¸ªæä¹ä¸ç¥éå 为å¥...
æ°æ®å±ç¤º
- role表
- user表
ä½¿ç¨ Navicatèªå¸¦çæ°æ®çæ
æ¥ä¸æ¥æä»¬ä½¿ç¨ Navicatçæ°æ®çæ
ç´æ¥ä¸ä¸æ¥, ç¶åéæ©å¯¹åºçä¸¤å¼ è¡¨çæè¡æ°å对åºççæè§å, åºäºä¹åçæ§è¡é度, è¿æ¬¡ roleçæ 1wæ°æ®, userçæ 10wæ°æ®
对äºå符串类åçå段, æ们å¯ä»¥è®¾ç½®ä»çéæºæ°æ®çæå¨, æ ¹æ®éè¦è¿è¡éæ©
ä¾å¦è§è²å称, éæ©äº èä½å称 è¿å¯ä»¥è¿è¡æ¯å¦å å« null çéæ©ç
ä½æ¯å¦ææ¯ å§å é£ä¹å°±ä¼è®©ä½ éæ©æ¯å¦å¯ä¸
æ°åçè¯ä¼è®©ä½ éæ©èå´, é»è®¤å¼ç
çç¡®å®å¥½äº, æ们就å¯ä»¥ç¹å»å³ä¸è§è¿è¡çæéæºæµè¯æ°æ®
éè¿ç»æå¯ä»¥çå°çæåä¸ä¸æµè¯æ°æ®ä¸å ±ç¨æ¶åä¸ç§ï¼ æ¯ç¬¬ä¸ç§æ¹æ³é度快å¾å¤ï¼ æ¨è使ç¨ï¼