SQLite 和 MySQL语法区别

SQLite 和 MySQL 在 SQL 语法上有一些差异，这些差异主要体现在数据类型、函数、表和索引的管理等方面。以下是一些主要的不同之处：

1. 数据类型

SQLite

• 支持的数据类型包括：TEXT, INTEGER, REAL, BLOB。
• 动态类型系统，允许在插入时自动转换数据类型。

MySQL

• 支持更丰富的数据类型，如：
  • 数值类型：INT, BIGINT, FLOAT, DOUBLE, DECIMAL 等。
  • 字符串类型：CHAR, VARCHAR, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT 等。
  • 时间日期类型：DATE, TIME, DATETIME, TIMESTAMP 等。
  • 二进制类型：BINARY, VARBINARY, BLOB 等。
  • 枚举类型：ENUM。
  • 集合类型：SET。

2. 创建表

SQLite

1CREATE TABLE example (
2    id INTEGER PRIMARY KEY,
3    name TEXT NOT NULL,
4    age INTEGER
5);

MySQL

1CREATE TABLE example (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(255) NOT NULL,
4    age INT
5);

3. 自增主键

SQLite

• 使用 AUTOINCREMENT 关键字。
• 也可以使用 INTEGER PRIMARY KEY 来实现自增主键。

MySQL

• 使用 AUTO_INCREMENT 关键字。

4. 修改表结构

SQLite

• 列操作只能添加列 

  您说得对，SQLite 从版本 3.35.0 开始支持直接添加列的功能。以下是更新后的语法：

  SQLite (版本 3.35.0 及以上):

  1ALTER TABLE example ADD COLUMN new_column_name data_type;

  1-- 创建一个新表，包含原表的所有列加上新的列
  2CREATE TABLE new_example AS SELECT column1, column2 FROM example;
  3-- 删除旧表
  4DROP TABLE example;
  5-- 重命名新表为旧表的名字
  6ALTER TABLE new_example RENAME TO example;

MySQL

• 添加列：

  1ALTER TABLE example ADD COLUMN new_column_name data_type;

• 删除列：

  1ALTER TABLE example DROP COLUMN column_name;

• 修改列的数据类型：

  1ALTER TABLE example MODIFY COLUMN column_name new_data_type;

• 修改列的名称：

  1ALTER TABLE example CHANGE COLUMN old_column_name new_column_name new_data_type;

5. 索引

SQLite

• 创建索引：

  1CREATE INDEX idx_name ON table_name (column_name);

• 删除索引：

  1DROP INDEX idx_name;

MySQL

• 创建索引：

  1CREATE INDEX idx_name ON table_name (column_name);

• 删除索引：

  1DROP INDEX idx_name ON table_name;

6. 函数

SQLite

• 常用函数：LOWER(), UPPER(), LENGTH(), SUBSTR(), REPLACE() 等。
• 没有 IFNULL() 函数，可以使用 COALESCE() 替代。

MySQL

• 常用函数：LOWER(), UPPER(), LENGTH(), SUBSTRING(), REPLACE(), IFNULL() 等。

7. 子查询

SQLite

• 支持子查询，但某些复杂子查询可能不被支持或性能较差。

MySQL

• 支持复杂的子查询，并且优化器通常能更好地处理子查询。

8. 联接（JOIN）

SQLite

• 支持标准的联接操作，如 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN。

MySQL

• 同样支持标准的联接操作，但在某些情况下可能会有不同的性能表现。

9. 事务

SQLite

• 支持事务，但默认是自动提交模式。
• 可以通过 BEGIN TRANSACTION; 开始一个事务，通过 COMMIT; 提交事务，通过 ROLLBACK; 回滚事务。

MySQL

• 支持事务，具体行为取决于所使用的存储引擎（如 InnoDB 支持事务，MyISAM 不支持）。
• 事务管理与 SQLite 类似，可以通过 BEGIN;, COMMIT;, ROLLBACK; 控制。

10. 其他特性

SQLite

• 支持部分正则表达式匹配。
• 支持虚拟表和模块化架构。

MySQL

• 支持视图、存储过程、触发器等高级特性。
• 支持分区表，可以在物理层面上将大表分成多个小表。