MySQL

SQL Fiddle

What's mysql?

MySQL is an open-source relational database management system (RDBMS) that provides scalable, reliable, and efficient storage of data. It is one of the most popular databases used for web applications and is widely used in the development of web-based software, including online stores, content management systems (CMS), and web-based applications. MySQL is compatible with various operating systems, and it provides developers with tools for data modeling, data querying, and data management. It is also a core component of the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) stack used by many developers for web development.

How many system database are there in MySQL? When to use them?

In MySQL, there are several system databases that serve different purposes. Here are some of the main system databases in MySQL:

information_schema: This database contains information about the database system itself, such as database schema, tables, columns, and indexes. It provides metadata about the databases and tables that are available on the MySQL server.
mysql: This database contains information about MySQL users and their privileges. It is used to manage user accounts and control access to databases.
performance_schema: This database provides performance-related information, such as server events, performance metrics, and resource usage statistics. It is useful for monitoring the performance of a MySQL server and identifying bottlenecks.
sys: This database provides a set of views and routines that simplify database administration tasks. It provides a more user-friendly interface for managing the MySQL server.

These system databases are typically used by database administrators to manage and monitor the MySQL server. They provide important information about the system, users, and performance of the server. Developers may also use them to optimize database queries and identify performance issues.

Do you mean the views and stored procedures are not in information_schema?

Views and stored procedures are not part of the system databases in MySQL. They are stored in regular user-defined databases, along with tables and other objects. The information_schema database, on the other hand, provides metadata about the objects that are stored in all databases on the MySQL server, including views and stored procedures.

For example, the information_schema.VIEWS table contains information about all views that exist on the MySQL server, including their names, the names of the tables they reference, and the SQL statements that define them. Similarly, the information_schema.ROUTINES table contains information about all stored procedures and functions on the server, including their names, parameters, and definition statements.

So while views and stored procedures are not system databases in MySQL, the information_schema database is still a useful tool for working with them and other database objects.

What do you mean by "metadata about the objects that are stored in all databases"? Does it mean there are some views or stored procedures shared across multi databases?

In MySQL, the term "metadata" refers to data about the structure of the database and its objects, such as tables, columns, views, stored procedures, and functions. The metadata provides information about the objects themselves, rather than the data that they contain.

The information_schema database is a system database in MySQL that contains metadata about all objects in all databases on the server. This means that it provides information about the structure of all databases, not just a single database.

For example, if you have two databases on your MySQL server, named "database1" and "database2", the information_schema database will contain metadata about all tables, views, and stored procedures in both databases.

To answer your second question, it is possible for views and stored procedures to be shared across multiple databases in MySQL. This can be done by referencing tables in other databases using the syntax "database_name.table_name". In this case, the view or stored procedure is defined in one database, but references tables in another database. The information_schema database would provide metadata about both the view or stored procedure, as well as the referenced tables in other databases.

What is utf8mb4? Is this a specific encoding only exists on MySQL?

UTF8mb4 is a character encoding in MySQL that is based on the UTF-8 character set. It is designed to support the full range of Unicode characters, including those outside the Basic Multilingual Plane (BMP), which includes emojis and other non-Latin characters.

UTF8mb4 is not a character encoding that only exists in MySQL, but rather a variation of the UTF-8 encoding that is widely used in many programming languages and systems. However, it is important to note that not all systems and applications support the full range of Unicode characters, and may use different character encodings depending on their specific requirements.

The "mb4" in UTF8mb4 stands for "4-byte UTF-8 encoding", which means that it can encode up to 4 bytes per character, allowing it to represent the full range of Unicode characters. The standard UTF-8 encoding, on the other hand, only supports up to 3 bytes per character, which limits the range of characters that can be represented.

UTF8mb4 is the default character encoding in MySQL 5.5.3 and later versions, and is recommended for most applications that need to support a wide range of international characters. It is also the recommended encoding for applications that need to store and display emojis and other non-Latin characters.

上面红色是错误观点

But I never heard UTF8mb4 encoding in c#, python or sql server

You are correct that the term "UTF8mb4" is not commonly used outside of MySQL. However, the concept of supporting 4-byte UTF-8 encoding is not unique to MySQL and is supported by many programming languages and systems, including C#, Python, and SQL Server.

For example, in C#, the UTF8Encoding class provides support for encoding and decoding Unicode characters using the UTF-8 encoding, including characters that require 4 bytes to represent. In Python, the built-in 'utf-8' codec supports the full range of Unicode characters, including those outside the Basic Multilingual Plane (BMP).

Similarly, in SQL Server, the 'nvarchar' data type supports the full range of Unicode characters, including those outside the BMP, using the UTF-16 encoding. While SQL Server does not use the term "UTF8mb4", the concept of supporting 4-byte Unicode characters is still present.

So while the specific term "UTF8mb4" may be unique to MySQL, the concept of supporting 4-byte UTF-8 encoding is not, and is widely supported in many programming languages and systems.

"The standard UTF-8 encoding, on the other hand, only supports up to 3 bytes per character", Are you sure? As I knew UTF-8 encodes code points in one to four bytes, depending on the value of the code point.

You are correct, and I apologize for the confusion in my previous answer. I misspoke when I said that the standard UTF-8 encoding only supports up to 3 bytes per character.

UTF-8 is a variable-length encoding that can represent any Unicode code point using one to four bytes, depending on the value of the code point. Code points in the Basic Multilingual Plane (BMP), which includes most commonly used characters, are represented using one to three bytes in UTF-8. However, code points outside the BMP, including many emojis and other non-Latin characters, require four bytes to represent in UTF-8.

UTF8mb4 in MySQL is a variation of the standard UTF-8 encoding that allows for four-byte characters, which is required to represent the full range of Unicode characters, including those outside the BMP. MySQL uses the term "mb4" to distinguish this encoding from earlier versions of MySQL, which used a limited version of UTF-8 that only allowed for up to three bytes per character.