MariaDB Crash Course - Ben Forta [81]
Input
ROLLBACK TO delete1;
* * *
Tip: The More Savepoints the Better
You can have as many savepoints as you want within your MariaDB SQL code, and the more the better. Why? Because the more savepoints you have the more flexibility you have in managing rollbacks exactly as you need them.
* * *
* * *
Note: Releasing Savepoints
Savepoints are automatically released after a transaction completes (a ROLLBACK or COMMIT is issued). Savepoints can also be explicitly released using RELEASE SAVEPOINT.
* * *
Changing the Default Commit Behavior
As already explained, the default MariaDB behavior is to automatically commit any and all changes. In other words, anytime you execute a MariaDB SQL statement, that statement is actually being performed against the tables, and the changes made occur immediately. To instruct MariaDB to not automatically commit changes, you need to use the following statement:
Input
SET autocommit=0;
Analysis
The autocommit flag determines whether changes are committed automatically without requiring a manual COMMIT statement. Setting autocommit to 0 (false) instructs MariaDB to not automatically commit changes (until the flag is set back to true).
* * *
Note: Flag Is Connection Specific
The autocommit flag is per connection, not serverwide.
* * *
Summary
In this chapter, you learned that transactions are blocks of SQL statements that must be executed as a batch. You learned how to use the COMMIT and ROLLBACK statements to explicitly manage when data is written and when it is undone. You also learned how to use savepoints to provide a greater level of control over rollback operations.
27. Globalization and Localization
In this chapter, you learn the basics of how MariaDB handles different character sets and languages.
Understanding Character Sets and Collation Sequences
Database tables are used to store and retrieve data. Different languages and character sets need to be stored and retrieved differently. As such, MariaDB needs to accommodate different character sets (different alphabets and characters) as well as different ways to sort and retrieve data.
When discussing multiple languages and characters sets, you will run into the following important terms:
• Character sets are collections of letters and symbols.
• Encodings are the internal representations of the members of a character set.
• Collations are the instructions that dictate how characters are to be compared.
* * *
Note: Why Collations Are Important
Sorting text in English is easy, right? Well, maybe not. Consider the words APE, apex, and Apple. Are they in the correct sorted order? That would depend on whether you wanted a case-sensitive or a not case-sensitive sorting. The words would be sorted one way using a case-sensitive collation, and another way using a not case-sensitive collation. And this affects more than just sorting (as in data sorted using ORDER BY); it also affects searches (whether or not a WHERE clause looking for apple finds APPLE, for example). The situation becomes more complex when characters such as the French è or German ö are used, and even more complex when non-Latin-based character sets are used (Japanese, Hebrew, Russian, and so on).
* * *
In MariaDB there is not much to worry about during regular database activity (SELECT, INSERT, and so forth). Rather, the decision as to which character set and collation to use occurs at the server, database, and table level.
Working with Character Set and Collation Sequences
MariaDB supports a vast number of character sets. To see the complete list of supported character sets, use this statement:
Input
SHOW CHARACTER SET;
Analysis
This statement displays all available character sets, along with the description and default collation for each.
To see the complete list of supported collations, use this statement:
Input
SHOW COLLATION;
Analysis
This statement displays