Original guide is in Hercules forum, I just import the information into rAthena since both emulator share most of the similar trait when using query_sql
and I guess the main reason is Olrox reminded me to do so ... because someone else might claim it as their own ... etc
Anyway, this guide is a compilation of SQL commands that I have used, or Questions answered on the forum
every single subject here are related to rAthena/Ragnarok Online in some ways, so you won't feel bored reading them XD
Table of Content
1. When to use *escape_sql script command
2. How to build a case-sensitive table
3. Choose a table type, MyISAM or InnoDB ?
3a. How to index a table properly
3b. Why you shouldn't use `char_reg_num` table
4. AUTO_INCREMENT
5. How to do IF-ELSE in SQL query ?
5a. How to update multiple rows on different conditions in a single query
6. How to show the current rank of the player
7. INSERT INTO ... SELECT ...
8. Table JOIN vs AS
9. What is the maximum string limit for *query_sql
9a. UNION
This topic will open to Suggestions, Ideas, Improvements, and Questions ~
You may also post up your tricks if you want to share with us
1. When to use escape_sql script command
ultramage said:
if the message contains " or ' or some such special character, then after it gets combined into a single string, it will not form a correct query
it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash
if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character
in rAthena script, we also know we can use " symbol in any string input
mes "Susan says :\" Today I ate 3 eggs \".";
where in the game client, you can see the " symbol in the npc msg box
let's say I have a sql script like this
prontera,153,171,5 script Show Characters 1_F_MARIA,{
mes "input name, I'll show you all characters name it has on that player's account";
input .@name$;
.@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `name` LIKE '"+ .@name$ +"'", .@cid, .@name$);
if ( !.@nb ) {
mes "no result";
close;
}
for ( .@i = 0; .@i < .@nb; ++.@i )
mes .@cid[.@i] +" "+ .@name$[.@i];
close;
}
this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack
BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack
just now that script was for string input
prontera,153,171,5 script Show Characters 1_F_MARIA,{
mes "input account ID, I'll show you all characters name it has on that player's account";
input .@aid$;
.@nb = query_sql("SELECT `char_id`, `name` FROM `char` WHERE `account_id` = "+ escape_sql(.@aid$), .@cid, .@name$);
if ( !.@nb ) {
mes "no result";
close;
}
for ( .@i = 0; .@i < .@nb; ++.@i )
mes .@cid[.@i] +" "+ .@name$[.@i];
close;
}
this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack
because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input
there are 2 ways to solve this
either use numeric variable for the input command
or enclose that ....
..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", ....
with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command
escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name
rAthena\conf\char_athena.conf
// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are:
// NOTE: Applies to character, party and guild names.
// 0: no restriction (default)
// 1: only letters/symbols in 'char_name_letters' option.
// 2: Letters/symbols in 'char_name_letters' option are forbidden. All others are possibles.
char_name_option: 1
and this was what happened to my SQL dota pvpladder script
Quote
I'm having this DB error with the script:
[SQL]: DB error - You have an error in your SQL syntax; check the manual that co
rresponds to your MySQL server version for the right syntax to use near 's Babie
s', 1 , 1 )' at line 1
[Debug]: at ..\src\map\script.c:11553 - insert into ownladder ( guild_id, name,
currentown, highestown ) values ( 98, 'Silo's Babies', 1 , 1 )
[Debug]: Source (NPC): DOTAPVP (invisible/not on a map)
I'm using SVN 12077. But this error only happens when that certain guild would have a kill.
Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark
with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query
Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )
After MySQL 5.6, (currently is 8.0)
just stick to InnoDB
there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB
There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB
prontera,155,186,6 script PVP Ladder 1_F_MARIA,{
.@nb = query_sql( "SELECT `name`, `kills`/(`death`+1) FROM `pvpladder` WHERE `kills` > 0 ORDER BY `kills`/(`death`+1) DESC LIMIT 10", .@name$, .@ratio$ );
if ( !.@nb ) {
mes "no entry";
close;
}
mes "Current Ranking :";
for ( .@i = 0; .@i < .@nb; ++.@i )
mes "No."+(.@i +1)+" ["+ .@name$[.@i] +"] "+ .@ratio$[.@i] +" kill";
close;
OnPCKillEvent:
if ( killedrid == getcharid(3) ) { // killing self should only increase death count. EG: Grand-cross
query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1";
end;
}
query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 1,0 ) ON DUPLICATE KEY UPDATE `kills` = `kills` +1";
attachrid killedrid;
query_sql "INSERT INTO `pvpladder` VALUES ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) )+"', 0,1 ) ON DUPLICATE KEY UPDATE `death` = `death` +1";
end;
}
This kind of query -> ORDER BY kills/death, needs to index them together like this
KEY (`kills`,`death`)
3b. Why you shouldn't use `char_reg_num` table
There are 2 reasons why you shouldn't even touch all these variable tables
Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration
Spoiler
rAthena/conf/map_athena.conf said:
// Database autosave time
// All characters are saved on this time in seconds (example:
// autosave of 60 secs with 60 characters online -> one char is saved every
// second)
autosave_time: 300
// Min database save intervals (in ms)
// Prevent saving characters faster than at this rate (prevents char-server
// save-load getting too high as character-count increases)
minsave_time: 100
// Apart from the autosave_time, players will also get saved when involved
// in the following (add as needed):
// 1: after every successful trade
// 2: after opening vending/every vending transaction
// 4: after closing storage/guild storage.
// 8: After hatching/returning to egg a pet.
// 16: After successfully sending a mail with attachment
// 32: After successfully submitting an item for auction
// 64: After successfully get/delete/complete a quest
// 128: After every bank transaction (deposit/withdraw)
// NOTE: These settings decrease the chance of dupes/lost items when there's a
// server crash at the expense of increasing the map/char server lag. If your
// server rarely crashes, but experiences interserver lag, you may want to set
// these off.
save_settings: 255
Reason no.2 The `value` field is not index !
Hunting Mission Line 146 said:
query_sql("SELECT char_id AS id, (SELECT `name` FROM `char` WHERE char_id = id),`value` FROM `char_reg_num` WHERE `key` = 'Mission_Total' ORDER BY `value` DESC LIMIT 5",.@id,.@name$,.@val);
This line has ORDER BY `value`, try recheck our main.sql file
CREATE TABLE IF NOT EXISTS `char_reg_num`(`char_id`int(11)unsigned NOT NULL default'0',`key` varchar(32) binary NOT NULL default'',`index`int(11)unsigned NOT NULL default'0',`value`int(11) NOT NULL default'0',
PRIMARY KEY (`char_id`,`key`,`index`),
KEY `char_id`(`char_id`)) ENGINE=MyISAM;
SQL will search through every single line in the `value` field if that column isn't index
Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use
Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !
query_sql "INSERT INTO `support_ticket` VALUES ( NULL, '"+ escape_sql(.@title$) ...
can retrieve the last row with
query_sql "SELECT MAX(`id`) FROM `support_ticket`", .@id;
// ----- OR -----
query_sql "SELECT LAST_INSERT_ID()", .@id;
Question : This question was asked on eathena forum board
One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value
| 1 | <data set 1>
| 2 | <data set 2>
| 3 | <data set 3>
| 25854 | <data set 4>
| 25855 | <data set 5>
| 25856 | <data set 6>
I want to make the value return back to normal as shown
| 1 | <data set 1>
| 2 | <data set 2>
| 3 | <data set 3>
| 4 | <data set 4>
| 5 | <data set 5>
| 6 | <data set 6>
How to do this WITHOUT losing any of the current data ?
Answer: The trick is ... just drop that column and rebuild it
ALTER TABLE `inventory` DROP COLUMN `id`;
ALTER TABLE `inventory` ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
convert the table into MyISAM will process the query much faster
5. How to do IF-ELSE in SQL query ?
Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly
query_sql "UPDATE `pvp_points` SET `points` = `points` - 1 WHERE `char_id` = "+ getcharid(0);
How do I make the points stop at 0 if the player is already at 0 points ?
Answer :
query_sql "UPDATE `pvp_points` SET `points` = IF(`points` = 0, 0, `points` - 1) WHERE `char_id` = "+ getcharid(0);
query_sql "UPDATE `pvp_points` SET `points` = (CASE WHEN `points` = 0 THEN 0 ELSE `points` - 1 END) WHERE `char_id` = "+ getcharid(0);
Explanations:
similar to rAthena script language,
if (<condition>)
<execute true condition>;
else
<execute false condition>;
5a. How to update multiple rows on different conditions in a single query
This query will update multiple rows on different condition
UPDATE `pvpladder` SET `points`=
CASE
WHEN `char_id`=150000 THEN `points`+1
WHEN `char_id`=150001 THEN `points`-1END
WHERE `char_id` IN (150000,150001);
build a large table, with 100,000 rows, and experiment with all of these
Spoiler
# --------------------------------------------------------------# Create a table, test with both InnoDB and MyISAM engine
create table testest (
account_id int(11) primary key,
name varchar(23),
kills int(11),
key (`kills` desc)) engine = innodb;
create table testest (
account_id int(11) primary key,
name varchar(23),
kills int(11)) engine = myisam;
explain testest;select*from testest order by kills desc limit 10;
truncate table testest;
drop table testest;
alter table testest engine = innodb;
alter table testest engine = myisam;
alter table testest add index (kills desc);
drop index kills on testest;# -----------------------------------------------------------------# Example script from www.1keydata.com# this method create additional rows by power of 2# if I have 100,000 rows, will create 10,000,000,000 rows then only start counting <-- bad methodselect t1.*, count(1)from testest t1, testest t2
where(t1.kills < t2.kills or t1.account_id = t2.account_id)groupby t1.account_id
order by t1.kills desc limit 20;select t1.*, count(1)from testest t1, testest t2
where(t1.kills < t2.kills or t1.account_id = t2.account_id)and t1.account_id =2000000groupby t1.account_id;# ------------------------------------------------------# Best answer from stackexchange# it has potential problem, the GROUP_CONCAT has string limit at 1024# when the resultset return over this string limit, FIND_IN_SET always return 0# although not useful for return current rank, this query is quite fast, probably good for return top 10 resultsselect*,
find_in_set( kills,(select group_concat( kills order by kills desc )from testest ))as rank
from testest order by kills desc limit 10;select*,
find_in_set( kills,(select group_concat( kills order by kills desc )from testest ))as rank
from testest where account_id =2000844;
SELECT length( GROUP_CONCAT(kills)) FROM testest;# --------------------------------------------------------# another answer from stackexchange# use count on a temporary table, very slow when list out multiple rows# but with InnoDB engine and `kills` field index, return result instantly when only 1 row selectedselect*,1+(SELECT count(1)from testest a WHERE a.kills > b.kills)from testest b
order by kills desc limit 10;select*,1+(SELECT count(1)from testest a WHERE a.kills > b.kills)from testest b
where account_id =2000844;# -------------------------------------------------------# a modify version from another answer in stackexchange# ORDER by `kills` always return instantly, make this query EXTREMELY useful for display TOP10
SELECT account_id,`name`,
IF (@score=s.kills,@rank:=@rank,@rank:=@rank+1) rank,@score:=s.kills score
FROM testest s,(SELECT @score:=0,@rank:=0) r
ORDER BY kills DESC limit 10;set@i:=221,@score:=3230,@rank:=23230;
SELECT account_id,`name`,
IF (@score=t.kills,@rank:=@rank,@rank:=@i) rank,@score:=t.kills,@i:=@i+1
FROM testest t
ORDER BY kills DESC limit 100;
SELECT account_id,`name`,
IF (@score=t.kills,@rank:=@rank,@rank:=@i) rank,@score:=t.kills,@i:=@i+1
FROM testest t,(SELECT @score:=0,@rank:=0,@i:=1)q
ORDER BY kills DESC limit 100;
SELECT `name`, IF(@d=t.kills,@r,@r:=@i),@d:=t.kills,@i:=@i+1
FROM testest t,(SELECT @d:=0,@r:=0,@i:=1)q
ORDER BY kills DESC limit 100;# Note the @i will start at 1, but it left the @i at 101 after this query is runselect@i;# return 101
with my MYSQL 5.7,
1. InnoDB return result faster when using ORDER BY
2. INDEX the `kills` field with DESC doesn't seems to make the result return faster ...
3. and if I drop the `kills` field index, with InnoDB table type return result 4x slower. MyISAM perform even worst
4. the example script provided by www.1keydata.com totally useless, I have to cancel execution after waiting for 60 seconds
Both Methods I've shown in the answer actually do the same things, but there are difference
Answer no.1 Method 1 use FIND_IN_SET + GROUP_CONCAT
this method return the result quite fast with InnoDB, but the GROUP_CONCAT can only show the string length at 1024
in other words, when exceeding this length, all other rank will be shown as 0, because the FIND_IN_SET cannot find the data available
Answer no.1 Method 2 use SQL variables order with number of rows
this method return result fastest even with MyISAM
however due to the variable insert with number of rows, doesn't allow OFFSET
Answer no.2 calculate the rank by comparing with a temporary table
this method runs slower, but with InnoDB engine AND `kills` field index, the result return instantly when only display 1 row of data
7. INSERT INTO ... SELECT ...
Question : This question was asked on eathena forum . Note: at the time the old mail system was unstable and not many server use it
I want to reward my players for supporting my server.
I want to give every player who are still actively login in this year (2010) an item ID 22574 in their storage
How do I achieve this ?
Answer : Run this SQL command when your server is offline
INSERT INTO `storage`(`account_id`,`nameid`,`amount`,`identify`)
SELECT `account_id`,'22574','1','1'
FROM `login`
WHERE DATE(`lastlogin`)>='2010-01-01'&&`account_id`!=1;
The reply from the topic starter gives a feed back, including this
This table is missing the `name` field. So have to retrieve the `name` from the `char` table.
A simple way is using table JOIN
SELECT `char`.`char_id`,`char`.`name`,`pvpladder`.`points`
FROM `pvpladder` LEFT JOIN `char` ON `pvpladder`.`char_id`=`char`.`char_id`
ORDER BY `pvpladder`.`points` DESC LIMIT 10;
However, there is an uncommon method, the same thing can be done using Aliases
SELECT `char_id` AS `CID`,(SELECT `name` FROM `char` WHERE `char_id`=`CID`),`points`
FROM `pvpladder` ORDER BY `points` DESC LIMIT 10;
In this example, both tables `char` and `pvpladder` have the `char_id` field index as PRIMARY KEY
and thus both examples return the result in same amount of time
However, there is a key difference on the optimization speed if the one of the table is not index properly
The below example, `item_id`.`id` field is index as PRIMARY KEY, but `mob_db`.`DropCardid` is not index
SELECT `mob_db`.`ID`,`mob_db`.`kname`,`item_db`.`id`,`item_db`.`name_japanese`
FROM `mob_db` LEFT JOIN `item_db` ON `mob_db`.`DropCardid`=`item_db`.`id`
WHERE `mob_db`.`DropCardid`>0;
SELECT `ID`,`kname`,`DropCardid` AS `ITEM_ID`,(SELECT `name_japanese` FROM `item_db` WHERE `id`=`ITEM_ID`)
FROM `mob_db` WHERE `mob_db`.`DropCardid`>0;
The 2nd query that uses Aliases will return result faster than table JOIN in this case
Conclusion : If use table JOIN, you have to keep in mind that the joined column has to be index
but if use AS, there is no need to consider this issue
9. What is the maximum string limit for *query_sql
Since query_sql sending the query as a string, we can actually use string manipulation script commands such as *sprintf and *implode
prontera,158,186,5 script test_mvp_card_name 1_F_MARIA,{
.@nb = query_sql( "SELECT `mvp_name`, `mvp_card_name` FROM `mvp_table`", .@name$, .@card_name$ );
for ( .@i = 0; .@i < .@nb; ++.@i )
mes .@name$[.@i] +" -> "+ .@card_name$[.@i];
close;
OnInit:
setarray .@mvpid[0], // taken from my MVP Ladder script
1086,// Golden Thief Bug 64
1115,// Eddga 65
1150,// Moonlight Flower 67
1159,// Phreeoni 69
1112,// Drake 70
1583,// Tao Gunka 70
1492,// Incantation Samurai 71
1046,// Doppelgangger 72
1252,// Garm 73
1418,// Evil Snake Lord 73
1059,// Mistress 74
1190,// Orc Lord 74
1087,// Orc Hero 77
1251,// Knight of Windstorm 77
1038,// Osiris 78
1658,// Ygnizem 79
1272,// Dark Lord 80
1871,// Fallen Bishop 80
1039,// Baphomet 81
1147,// Maya 81
1785,// Atroce 82
1389,// Dracula 85
1630,// Bacsojin 85
1885,// Gorynych 85
1623,// RSX 0806 86
1511,// Amon Ra 88
1688,// Lady Tanee 89
1768,// Gloom Under Night 89
1719,// Datale 90
1734,// Kiel D-01 90
1157,// Pharaoh 93
1373,// Lord of Death 94
1312,// Turtle General 97
1779,// Ktullanux 98
1874,// Beelzebub 98
1646,// Bio3 placeholder 99
1708,// Thanatos 99
1751,// Valkyrie Randgris 99
1832;// Ifrit 99
.@size = getarraysize( .@mvpid );
// query_sql "DROP TABLE `mvp_table`";
query_sql "CREATE TABLE IF NOT EXISTS `mvp_table` (`mvp_id` SMALLINT(6) PRIMARY KEY, `mvp_name` VARCHAR(30), `mvp_card` SMALLINT(6), `mvp_card_name` VARCHAR(30) ) ENGINE = MyISAM";
for ( .@i = 0; .@i < .@size; ++.@i )
.@values$[.@i] = "( "+ .@mvpid[.@i] +", (SELECT `kname` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `DropCardid` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `item_db`.`name_japanese` FROM `item_db` LEFT JOIN `mob_db` ON `item_db`.`id` = `mob_db`.`DropCardid` WHERE `mob_db`.`ID` = "+ .@mvpid[.@i] +") )";
query_sql "INSERT IGNORE INTO `mvp_table` VALUES "+ implode( .@values$, ", " );
end;
}
for ( .@i = 0; .@i < .@size; ++.@i )
.@values$[.@i] = "( "+ .@mvpid[.@i] +", (SELECT `kname` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `DropCardid` FROM `mob_db` WHERE ID = "+ .@mvpid[.@i] +"), (SELECT `item_db`.`name_japanese` FROM `item_db` LEFT JOIN `mob_db` ON `item_db`.`id` = `mob_db`.`DropCardid` WHERE `mob_db`.`ID` = "+ .@mvpid[.@i] +") )";
query_sql "INSERT IGNORE INTO `mvp_table` VALUES "+ implode( .@values$, ", " );
This part ... can be optimize with UNION
for ( .@i = 0; .@i < .@size; ++.@i )
.@values$[.@i] = "SELECT "+ .@mvpid[.@i] +", `kname`, `DropCardid` AS `MVP_CARD`, (SELECT `name_japanese` FROM `item_db` WHERE `id` = `MVP_CARD`) FROM `mob_db` WHERE ID = "+ .@mvpid[.@i];
query_sql "INSERT IGNORE INTO `mvp_table` "+ implode( .@values$, " UNION " );
DONE ---- FINALLY !!
Yes, I knew the chapter 5 and beyond is very tough to understand ... it also took me longer time to write and test all these advance SQL techniques too
Anyway, this topic is now open to Suggestions, Ideas, Improvements, and Questions ~
You may also post up your tricks if you want to share with us
Question
AnnieRuru
Original guide is in Hercules forum, I just import the information into rAthena since both emulator share most of the similar trait when using query_sql
and I guess the main reason is Olrox reminded me to do so ... because someone else might claim it as their own ... etc
Anyway, this guide is a compilation of SQL commands that I have used, or Questions answered on the forum
every single subject here are related to rAthena/Ragnarok Online in some ways, so you won't feel bored reading them XD
Table of Content
1. When to use *escape_sql script command
2. How to build a case-sensitive table
3. Choose a table type, MyISAM or InnoDB ?
3a. How to index a table properly
3b. Why you shouldn't use `char_reg_num` table
4. AUTO_INCREMENT
5. How to do IF-ELSE in SQL query ?
5a. How to update multiple rows on different conditions in a single query
6. How to show the current rank of the player
7. INSERT INTO ... SELECT ...
8. Table JOIN vs AS
9. What is the maximum string limit for *query_sql
9a. UNION
This topic will open to Suggestions, Ideas, Improvements, and Questions ~
You may also post up your tricks if you want to share with us
1. When to use escape_sql script command
.
it doesn't has much differences, because it only affect 3 special characters
' <- single quotation mark
" <- double quotation mark
\ <- left slash
if I input -> haha"lala'hehe <-
it will return -> haha\"lala\'hehe <-
this is what we call, Escape a character
in rAthena script, we also know we can use " symbol in any string input
mes "Susan says :\" Today I ate 3 eggs \".";
where in the game client, you can see the " symbol in the npc msg box
let's say I have a sql script like this
this script has a possibility to be hacked
because to perform sql injection, I can enclose the string with quotation mark, then use another sql command to hack
BUT with an escape_sql command, if the user want to enclose the string with quotation mark to hack the script
the escape_sql command escaped the string, the quotation mark the user input will be escaped
thus the script will become impossible to hack
just now that script was for string input
this is another stupid case.
1. the scripter use string input while the script just needed a number
2. even with escape_sql command over there, there is no quotation mark at all
yes this script also has a risk to be hack
because escape_sql only escape quotation mark.
that hacker don't even have to input quotation mark because it is a number
and an injection query can be sent without any quotation mark input
there are 2 ways to solve this
either use numeric variable for the input command
or enclose that ....
..... WHERE `account_id` = '"+ escape_sql(.@aid$) +"'", ....
with single quotation mark, when the hacker input a quotation mark will be escaped by escape_sql command
Reference : https://www.w3schools.com/sql/sql_injection.asp
escape_sql command for another thing is
if the player register their names containing ' or ", these characters are escaped
only happens when the server have no restriction on the creation of players name
rAthena\conf\char_athena.conf
and this was what happened to my SQL dota pvpladder script
Silo's Babies <-- this is a guild name
you can see the 5th string has a single quotation mark
with escape_sql command, that string will turn into
Silo\'s Babies <-- the quotation mark is escaped when send to sql query
2. How to build a case-sensitive table
this is the answer I found
Reference : http://dba.stackexchange.com/questions/15250/how-to-do-a-case-sensitive-search-in-where-clause
by default, the table creation use charset = latin1;
means it couldn't do a case-sensitive search
if you want to do a case-sensitive in a query, use BINARY
however using BINARY might have performance hit if it is a big table
so its more recommend to convert your SQL table to collate with latin1_general_cs
let's say this is a sample table
do an ALTER table syntax
or just put it into the table creation
3. Choose a table type, MyISAM or InnoDB ?
https://stackoverflow.com/questions/20148/myisam-versus-innodb
Before MySQL 5.5,
MyISAM is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )
InnoDB is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )
After MySQL 5.6, (currently is 8.0)
just stick to InnoDB
there is only 1 reason MyISAM is better than InnoDB
- MyISAM use smaller disk usage than InnoDB
let's take a look at rAthena MyISAM to InnoDB converter
https://github.com/rathena/rathena/blob/master/sql-files/tools/convert_engine_innodb.sql
This converter is useful if you are using MySQL 5.6 or above
There are 4 tables that are commented out
the reason is simple, these 4 tables only read once and forgotten when server is live
since MyISAM is good at reading (SELECT) + smaller disk usage, its no use to convert these 4 tables into InnoDB
3a. How to index a table properly
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://www.dbta.com/Columns/DBA-Corner/Top-10-Steps-to-Building-Useful-Database-Indexes-100498.aspx
a simple thumb of rule, anything that is SELECT .... WHERE `field` = .....
that `field` has to be index
let's take a look at this PVP Ladder script that use Kill/Death ratio
This kind of query -> ORDER BY kills/death, needs to index them together like this
3b. Why you shouldn't use `char_reg_num` table
There are 2 reasons why you shouldn't even touch all these variable tables
Reason no.1 This table is sorely meant for server usage
Once these data is loaded, it is process internally, and only save character data according to this configuration
Reason no.2 The `value` field is not index !
This line has ORDER BY `value`, try recheck our main.sql file
SQL will search through every single line in the `value` field if that column isn't index
Of course you can ... do ALTER table to add KEY to the `value` field
but this table has already optimized in that way for server usage
the more field you index into the table, the more disk usage space it use
Conclusion : If you want to make a custom script, then make a custom table. Leave these table alone !
4. AUTO_INCREMENT
In this kind of query that has AUTO_INCREMENT, many people do ....
can be optimize .... using NULL
can retrieve the last row with
Question : This question was asked on eathena forum board
One of my friend touched my custom table and the AUTO_INCREMENT has jump off the value
I want to make the value return back to normal as shown
How to do this WITHOUT losing any of the current data ?
Answer: The trick is ... just drop that column and rebuild it
convert the table into MyISAM will process the query much faster
5. How to do IF-ELSE in SQL query ?
Question : I have a PVP ladder script that runs on Points system. Each kill plus 1 point and each death minus 1 point.
The problem is, this query will make the points go into negative value if the player is being kill repeatedly
How do I make the points stop at 0 if the player is already at 0 points ?
Answer :
Explanations:
similar to rAthena script language,
in SQL language
Reference : https://www.w3schools.com/sql/func_mysql_if.asp
https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select
5a. How to update multiple rows on different conditions in a single query
This query will update multiple rows on different condition
Reference : https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query
6. How to show the current rank of the player
Question : This is the part of the script, output as below
No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.3 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills
2nd place and 3rd place has the same amount of kills, how do I make both of them display as 2nd place like this ?
No.1 [Alice] ~ 19 kills No.2 [Emily] ~ 11 kills No.2 [Irene] ~ 11 kills No.4 [Brittany] ~ 8 kills No.5 [Fiona] ~ 7 kills
Answer :
Method no.1: Convert the table into InnoDB will return the result faster. Allow to use OFFSET
Method no.2: This method return result faster than method 1 in huge table. Not allow to use OFFSET
.
.
Question : How do I show the current ranking of the player ?
Answer :
Remember to index the `kills` field
Reference : https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table
.
MySQL doesn't support RANK() unlike other SQL program
so let's test with all the methods around the internet
build a large table, with 100,000 rows, and experiment with all of these
with my MYSQL 5.7,
1. InnoDB return result faster when using ORDER BY
2. INDEX the `kills` field with DESC doesn't seems to make the result return faster ...
3. and if I drop the `kills` field index, with InnoDB table type return result 4x slower. MyISAM perform even worst
4. the example script provided by www.1keydata.com totally useless, I have to cancel execution after waiting for 60 seconds
Both Methods I've shown in the answer actually do the same things, but there are difference
Answer no.1 Method 1 use FIND_IN_SET + GROUP_CONCAT
this method return the result quite fast with InnoDB, but the GROUP_CONCAT can only show the string length at 1024
in other words, when exceeding this length, all other rank will be shown as 0, because the FIND_IN_SET cannot find the data available
Answer no.1 Method 2 use SQL variables order with number of rows
this method return result fastest even with MyISAM
however due to the variable insert with number of rows, doesn't allow OFFSET
Answer no.2 calculate the rank by comparing with a temporary table
this method runs slower, but with InnoDB engine AND `kills` field index, the result return instantly when only display 1 row of data
7. INSERT INTO ... SELECT ...
Question : This question was asked on eathena forum . Note: at the time the old mail system was unstable and not many server use it
I want to reward my players for supporting my server.
I want to give every player who are still actively login in this year (2010) an item ID 22574 in their storage
How do I achieve this ?
Answer : Run this SQL command when your server is offline
The reply from the topic starter gives a feed back, including this
8. Table JOIN vs AS
This table is missing the `name` field. So have to retrieve the `name` from the `char` table.
A simple way is using table JOIN
However, there is an uncommon method, the same thing can be done using Aliases
In this example, both tables `char` and `pvpladder` have the `char_id` field index as PRIMARY KEY
and thus both examples return the result in same amount of time
However, there is a key difference on the optimization speed if the one of the table is not index properly
The below example, `item_id`.`id` field is index as PRIMARY KEY, but `mob_db`.`DropCardid` is not index
The 2nd query that uses Aliases will return result faster than table JOIN in this case
Conclusion : If use table JOIN, you have to keep in mind that the joined column has to be index
but if use AS, there is no need to consider this issue
9. What is the maximum string limit for *query_sql
Since query_sql sending the query as a string, we can actually use string manipulation script commands such as *sprintf and *implode
Example : *sprintf
Example : *implode
So, someone might ask, what is the string limit for query_sql until the map-server show error ?
The answer is very surprising ... I just tested with above script, and it still works perfectly fine !
loop -> 64.210 seconds
query -> 3.229 seconds
it means, ahh .... or maybe ...
The answer might be ...
until MySQL stop responding XD
This src\common\showmsg.c is just a display error message ...
this means, if your query is bug, it only display the string length until this limit
9a. UNION
Just now the *implode example, it just shows the `mvp_id`, but if we want to display the information like this,
This part ... can be optimize with UNION
DONE ---- FINALLY !!
Yes, I knew the chapter 5 and beyond is very tough to understand ... it also took me longer time to write and test all these advance SQL techniques too
Anyway, this topic is now open to Suggestions, Ideas, Improvements, and Questions ~
Edited by AnnieRuruYou may also post up your tricks if you want to share with us
Link to comment
Share on other sites
2 answers to this question
Recommended Posts