The difference and application of SQLite and MySQL database

In simple terms,SQLite features simple. Miniaturization, the pursuit of maximum disk efficiency;MySQL features comprehensive, integrated. The pursuit of maximum concurrency efficiency. Assuming that it is only used on a single machine, the amount of data is not very large. Need to move easilyFrequently read/write disk files. It is more appropriate to use SQLite, assuming that you want to meet multiple users at the same time. or the number of site visits is relatively large is the use of MySQL is more appropriate.

The differences and applications of the two are detailed below:

SQLite
SQLite is an extraordinary database in which he is able to process in applications that use it. As a self-contained, file-based database, SQLite provides a great toolset to handle all types of data with no limitations and is much easier to use than the server-executed process server.

when an app uses SQLite. Its functions are directly integrated into the. Instead of interacting with some ports (port, socket), the app directly visits files that include data (that is, the SQLite database). Thanks to the underlying technology, this makes SQLite very fast and efficient, and very powerful.

data types supported by SQLite

NULL:

null value.

INTEGER:

a signed integer. stored in 1, 2, 3, 4, 6, or 8 bytes according to the settings.

REAL:

floating-point number. Stored using the 8-byte IEEE floating-point number method.

TEXT:

A text string that is stored using the database encoding (UTF-8, utf-16be, or Utf-16le).

BLOB:

Binary large object, How to enter it is how to store it.

Note: Want to know a lot of other information about SQLite data types. The ability to view official documents for this topic.

SQLite The advantages of

File-based:

The entire database is included in a file on disk, so it has a very good migration nature.

normalization:

Although it looks like a "simplified version "Database, SQLite does support SQL.

It omits functionality (right OUTER JOIN and for each STATEMENT), but adds some additional functionality at the same time.

It's great for development and even testing:

In most application development phases, most people need to solve the problem in a way that has concurrent flexibility.

SQLite has a rich functional base that can be delivered beyond development and is concise enough to require only a single file and a C-link library.

The disadvantage of SQLite

No Practical user management:

Advanced databases can support user systems. For example, you can manage access to databases and tables for database connections.

But because of the purpose of SQLite and its nature (no multi-user concurrency of high-level design). It does not have this function.

lack of flexibility for additional optimization performance:

still from the beginning of the design, SQLite does not support the use of various techniques for additional performance optimizations. This library easy to configure, easy to use. Since it is not complicated, it is theoretically impossible to make it faster than it is today, in fact it is now very fast.

when to use SQLite?

Embedded Applications:

all need to be migrated, no need to expand the application, such as. Single-user local apps, mobile apps and games.

Replace disk interview:

in very many cases. Applications that require frequent direct read/write disk files are well suited for converting to SQLite, benefiting from the functionality and simplicity of SQLite using SQL.

Test:

It can kill most of the special needles corresponding to the business logic (that is, the main purpose of the application: can complete the function) test.

When to use SQLite?

Multi-user applications:

Assuming that you are developing an application that needs to be visited by multiple users, and that these users are using the same database, then SQLite may choose a fully functional relational database (such as MySQL).

Applications that require a large area of data writing:

One of the drawbacks of SQLite is its write operation. This database only agrees to a write operation at the same time. Therefore, throughput is limited.

MySQL
MySQL is one of the most popular in all large database servers. It's rich in features. The open source nature of the product allows it to drive a large number of sites and applications online. To get started MySQL is relatively simple, and developers can access a lot of information about the database on the Internet.

Note: Because of the popularity of this product, a large number of third-party applications, tools, and integrated libraries can be helpful in all aspects of operating this rdbcms.

MySQL does not attempt to implement all of the SQL standards, but provides users with a lot of useful functionality. As a standalone database server, the application interacts with the MySQL daemon, telling it to access the database itself-unlike SQLite.

data types supported by MySQL

TINYINT:

a very small integer.

SMALLINT:

a small integer.

Mediumint:

An integer of an intermediate size.

INT or INTEGER:

a normal-sized integer.

BIGINT:

a large integer.

FLOAT:

a small (single-precision) floating-point number. It can't be the unsigned kind.

double, double PRECISION, REAL:

a normal-sized (double-precision) floating-point number that cannot be made unsigned.

DECIMAL, NUMERIC:

There are no packed floating-point numbers. Can't make the kind of unsigned.

DATE:

a date.

DATETIME:

a combination of date and time.

TIMESTAMP:

a time stamp.

Time :

a time.

Year :

a year in two-bit or 4-digit format (the default is 4-bit).

CHAR:

a fixed-length string. Storage is always right-aligned in its fixed-length space.

VARCHAR:

a variable-length string.

Tinyblob, Tinytext:

a BLOB or text column. The maximum length of 255 (2^8-1) characters.

BLOB, TEXT:

a BLOB or text column with a maximum length of 65535 (2^16-1) characters.

Mediumblob, Mediumtext:

a BLOB or text column with a maximum length of 16777215 (2^24-1) characters.

Longblob, Longtext:

a BLOB or text column. The maximum length of 4294967295 (2^32-1) characters.

ENUM:

One of the enumeration types.

SET:

a collection.

MySQL's strengths

Easy to use:

installing MySQL is easy. Third-party libraries, which contain visualizations (i.e. with GUIs), make it easy to get started with a database.

feature rich:

MySQL supports most of the SQL features that a relational database should have--some direct support. Some indirect support.

Safety:

MYSQL has a lot of security features, some of which are quite advanced.

flexible and powerful:

MySQL Can handle very much data, and if necessary, it can "adapt" to data of all sizes.

High speed:

Discard support for certain standards. Make MySQL more efficient and use shortcuts, resulting in increased speed.

mysql disadvantages

known limitations:

Reliability issues:

The way MySQL implements certain features (for example, references). Transaction. Data auditing, etc.) makes it somewhat less reliable than some other relational databases.

Development stagnation:

Although MySQL is theoretically still an open source product. Others complain that it has been slow to update since it was born. However. It should be noted that there are MySQL-based and fully integrated databases (such as MariaDB). Provides added value on standard MySQL basis.

when to use MySQL?

Distributed operations:

when SQLite doesn't meet your needs, you can include MySQL in your deployment stack. Like no matter what a standalone database server, it brings a lot of freedom of operation and some advanced features.

High Security:

MySQL's security features provide reliable protection for data access (and use) in a simple way.

Web sites and Web apps:

The vast majority of sites (and Web applications) can ignore the constraints of simply working on MySQL.

Such flexible and extensible tools are easy to use and manageable-which proves to be useful for long-term execution.

Custom solutions:

Assuming you're working on a highly tailored solution, MySQL can easily follow and run your rules, thanks to its rich configuration settings and operating modes.

When is MySQL not used?

SQL Compliance:

Because MySQL does not [want to] implement all the standards of SQL. So this tool does not fully conform to SQL.

It is not easy to switch from MySQL if you need to integrate this relational database management system.

Concurrent:

Even though MySQL and some storage engines can really run the read operation very well, there is a problem with concurrent read and write.

Lack of features:

Again, according to the selection criteria of the database engine, MySQL lacks certain features, such as full-text search.

The difference and application of SQLite and MySQL database