MySQL 5.7.6 supports full-text index for Chinese, Japanese, and Korean (CJK).
The built-in MySQL full-text parser uses the white space between words as a delimiter to determine where words begin and end, which is a limitation when working with ideographic languages that do not use word delimiters. To address this limitation, MySQL provides an ngram full-text parser that supports Chinese, Japanese, and Korean (CJK). The ngram full-text parser is supported for use with InnoDB and MyISAM.
Create a Full-Text Index
Creating a FULLTEXT Index that Uses the ngram Parser
CREATE FULLTEXT INDEX content_fulltext ON table_name(column1, column2,...) with parser ngram;
Full-Text Searches
Natural Language Full-Text Searches
SELECTCOUNT(*) FROM articles WHEREMATCH (title,body) AGAINST ('database'INNATURALLANGUAGE MODE); -- or SELECTCOUNT(*) FROM articles WHEREMATCH (title,body) AGAINST ('database');
the columns named in the MATCH() function (title and body) are the same as those named in the definition of the article table’s FULLTEXT index. To search the title or body separately, you would create separate FULLTEXT indexes for each column.
Boolean Full-Text Searches
-- retrieves all the rows that contain the word “MySQL” but that do not contain the word “YourSQL” SELECT*FROM articles WHEREMATCH (title,body) AGAINST ('+MySQL -YourSQL'INBOOLEAN MODE);
+ stands for AND
- stands for NOT
[no operator] implies OR. The word is optional, but the rows that contain it are rated higher.
“”. A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed.
If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.
MySQL you are discouraged from keeping TEXT data (and BLOB, as written elsewhere) in tables frequently searched.
:checked //Matches all elements that are checked or selected. :selected $("option:selected[value=1]") :focus //Selects element if it is currently focused. :disabled :enabled //Selects all elements that are enabled.
:header // select all header elements, such as <H1>,<H2> :animated
Filter Methods
Filter Methods
filter("p") // get all <p> elements in jquery dom set $("li").has("span") //get all li has child element 'span' not("p, .name") // get all not <p> and class=name elements in jquery dom set
Position Method
first() last() eq("") // index begin with 0
Hierarchical Methods
Select parent or ancestors
parent() // one parent parents() // all ancestors $("span").parents("ul"); // all ancestors $("span").parentsUntil("div") // returns all ancestor elements between two given arguments
Select children or descendants
children("") children() find(""); // find descendants // the find() is similar to the children() method，but childern() only find children not descendants.
Select siblings
siblings() siblings("") // get other dom of same level, don't contain self next() nextAll() nextUntil() prev() prevAll() prevUntil()
Convert a complex SQL statement to multiple simple SQL statements
Convert queries to use indexes. Using indexes operators: =, >, <, >=, <=, BETWEEN, IN. Not using indexes operators: LIKE, <>, NOT IN. Using MySQL built-in functions on column make query cannot use index.
NOT IN
Using LEFT JOIN or NOT EXISTS to replace NOT IN
A NOT IN query example
select* from table_a where table_a.id isnotin ( select aid from table_b where table_b.status =1) -- or select* from table_a where table_a.id isnotin (1,2,3, ...)
Use NOT EXISTS
select* from table_a wherenotexists ( select*from table_b where table_a.id = table_b.aid and table_b.status =1 )
Use LEFT JOIN
select* from table_a leftjoin table_b on table_a.id=table_b.aid and table_b.status =1 where table_b.id isnull;
LIKE
Using Full-text search MATCH ... AGAINST... or search engine replace LIKE
OR
Using IN, or UNION, or AND NOT to replace OR
ORs are notoriously bad performers, because it splinters the execution path. The UNION alleviates that splintering, and the combines the two results sets. That said, IN is preferable to ORs because though being logically the same, the execution of IN is generally more optimized.
The OR query example 1:
select* from {table_name} where name = "Tom" or name = "Jack"
Using IN
select* from {table_name} where name in ("Tom", "Jack")
The OR query example 2:
select* from {table_name} where name like "T%" or name = "J%"
Using UNION
select* from {table_name} where name like "T%" union select* from {table_name} where name like "J%"
The OR query example 3:
select* from {table_name} where {col1} = "a" or {col2} = "b"
Using AND NOT
select* from {table_name} where {col1} = "a" and {col2} != "b"; select* from {table_name} where {col2} = "b";
Built-in Functions
Avoid using functions in predicates
Use BETWEEN to replace DATE(), DATE_FORMAT()
A DATE_FORMAT example:
DATE_FORMAT(`date`, "%Y-%m") = "2022-07"
YEAR(`date`) = "2022" andMONTH(`date`) = "7"
Use BETWEEN
`date` BETWEEN'2022-07-01'and'2022-07-31'
Convert function to use on right side
WHERE f(g(h(column))) >value
WHEREcolumn> H(G(F(value)))
Page Query
Basic Optimization
Query only the necessary columns instead of select *
Query part of the TEXT column by SUBSTR(column, start, length) if it could be.
High Offset Page Query Improvement
Late row lookups: Inner join ids instead of select *
Inner join is first to find offset + size rows id, then find all rows data by id set
select * is just to find offset + size rows data.
select *
select* from {table_name} as a where ... limit {x}, 10
Inner join ids
select* from {table_name} as a innerjoin ( select id from {table_name} where ... orderby id limit {x}, 10 ) as b on a.id=b.id;
In most cases, the inner join has a 5x performance improvement, but in some cases there is no difference in performance.
The performance gap is more evident for large row-width tables and queries with large offset.
Next Page Query Improvement
Specify start row id for next page query
first page query
select* from {table_name} as a innerjoin ( select id from {table_name} where ... orderby id limit x, 10 ) as b on a.id=b.id;
next page query
select* from {table_name} as a innerjoin ( select id from {table_name} where id > {last_page_max_id} and ... orderby id limit 10 ) as b on a.id=b.id;
Cache Page Query
Caching total row number for the same condition queries.
Caching every page start row id value and end row id value for the same condition queries.
The jdbcType is a property for mapping Java object field values to SQL column values.
You need to specify the jdbcType when passing null values for parameters. Some databases need to know the value’s type even if the value itself is NULL. The JDBC specification itself that requires the type to be specified.
preparedStatement.setNull(int parameterIndex, int sqlType)
Most of the times you don’t need to specify the jdbcType as MyBatis is smart enough to figure out the type from the objects you are working with. But if you send your parameters to the MyBatis statement inside a HashMap, for example, and one of the parameters is null, MyBatis won’t be able to determine the type of the parameter by looking at the HashMap because the HashMap is just a generic container and null itself carries no type information. At that point it would be o good idea to provide the jdbcType so that switching the database implementation later on does not cause any issues with null values.
MySQL and Java DB currently do not support the ARRAY SQL data type.
Update default mappings of Java values to column values
Convert java.util.Date objects to date strings
The java.util.Date objects will default convert to datetime strings (TIMESTAMP JDBC type) like 2022-08-18 11:12:05.545. If you want to convert a Date object value to a date string (DATE JDBC type) like 2022-08-18, you can specify the jdbcType to DATE.
javaType
The javaType is a property for mapping result set column values to Java field values.
MyBatis can usually figure out the type if you’re mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behavior.
typeHandler
typeHandler can convert column values to Java values and verse vice.
Convert between the Java Enum type value and the column value
let multipleLineStr = `hello world `; // or let multipleLineStr2 = 'hello\n'+ 'world'; // or let multipleLineStr3 = 'hello\n\ world';
Remove HTML tags from a string
let htmlStr = "<div><a href='baidu.com'>test</a></div>"; htmlStr = htmlStr.replace(/<\/?[^>]+(>|$)/g, ""); console.log(htmlStr) // test
Replace newline with <br>
let str = `hello world JS`; str.replace(/(\r|\n|\r\n)/g, "<br>"); // or str.replaceAll("\r", "<br>").replaceAll("\n", "<br>").replaceAll("\r\n", "<br>");
Reverse string
let s = 'Hello'; // ASCII characters s.split("").reverse().join(""); // 'olleH'
let s = '😀😂'; // supports UTF-16 or other multi-byte characters [...s].reverse().join(""); // '😂😀'
Date Object
date to timestamp
const timestamp = newDate().getTime();
timestamp to date
const date = newDate(timestamp)
Type Check
Check null or undefined
value == null
Check undefined
typeof value === 'undefined'
Check null
value === null
Check string type
typeof value === 'string' || value instanceofString
Check number type
typeof value === 'number'
Check object or JSON object
typeof json === "object" && !Array.isArray(value)
Check array or JSON array type
let value = [1, 2, 3]; typeof value === "object" && Array.isArray(value);
Check date object type
let date = newDate(); Object.prototype.toString.call(date) === '[object Date]'
var width = document.documentElement.clientWidth; var height = document.documentElement.clientHeight; let resolution = `your screen resolution is ${width} * ${height}`
String Format with regular expressions
let str = 'My Name is ${name}. His name is ${name}'; let replacement = "John"; str.replace(/${\w}/, replacement);
Add leading zero
let date = 1; let totalLenght = 2; let result = String(date).padStart(totalLength, '0'); // '01'
functionpad(num, size) { num = num.toString(); while (num.length < size) num = "0" + num; return num; }
Number Format
let num = 34.7698; let numFixed = num.toFixed(2);
Date Format
using slice
// date object to UTC datetime string yyyy/MM/dd HH:mm:ss const str = (newDate()).toISOString().slice(0, 19).replace(/-/g, "/").replace("T", " ");
// date object to local time string yyyy/MM/dd HH:mm:ss const now = newDate(); const offsetMs = now.getTimezoneOffset() * 60 * 1000; const dateLocal = newDate(now.getTime() - offsetMs); const str = dateLocal.toISOString().slice(0, 19).replace(/-/g, "/").replace("T", " ");
Using date object properties
var today = newDate(); var dd = String(today.getDate()).padStart(2, '0'); var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0! var yyyy = today.getFullYear();
today = mm + '/' + dd + '/' + yyyy;
Type Conversion
Number to String
let num = 1; let reslut = num.toString() // '1'
let num = 1; let reslut = num + ''; // '1'
String to Number
parseInt(string, radix)
Number(string)
let s = "1"; let reslut = parseInt(s); // 1
let s = "1"; let reslut = Number(s); // 1
String to Float Number
parseFloat()
Deep Copy
Using JSON.stringify() and JSON.parse()
Pros: deep copies nested objects
Cons: doesn’t copy functions
let clone = JSON.parse(JSON.stringify(object));
Using Object.assign()
Pros: copies the immediate members of an object—including functions.
Cons: doesn’t deep copy nested objects
let clone = Object.assign({}, object);
Using spread operator
Pros: simple syntax, the preferred way to copy an object