轻量级 PHP 数据库框架,Medoo入门, Medoo使用指南, Medoo中文文档, Medoo数据库基本操作, Medoo安装和配置, Medoo的使用教程

轻量级 PHP 数据库框架,Medoo入门, Medoo使用指南, Medoo中文文档, Medoo数据库基本操作, Medoo安装和配置, Medoo的使用教程
轻量级 PHP 数据库框架,Medoo入门, Medoo使用指南, Medoo中文文档, Medoo数据库基本操作, Medoo安装和配置, Medoo的使用教程

什么是 Medoo [`medu:]

Medoo 这一单词来自拉丁语 med (middle),即:你可以PHP和数据库之间  你想要的一切。

 

为什么选择 Medoo

Medoo 非常轻量级且只有一个文件,它易于使用,易于学习掌握以及高性能,这可以增加 web 应用程序的开发体验和用户体验。它适合于每个需要 SQL 数据库的 PHP 开发项目。

  • 非常的轻量

    未压缩只有 34KB。

  • 简单

    非常的容易学习,快速上手。

  • 强大

    支持各种常见的SQL查询。

  • 兼容

    支持各种数据:MySQL, MSSQL, SQLite, MariaDB, Oracle, Sybase, PostgreSQL等等

  • 安全

    防止SQL注入

  • 免费

    MIT 协议, 你可以进行任何修改。

 

下载 Medoo

官网:https://medoo.in/

直接下载: Version: 1.1.2 [23KB]    或者  Version: 1.7.10 [34KB]  或者 Version: 2.1.4

 

使用步骤

依赖安装

$ composer require catfan/Medoo

 

更新

$ composer update

 

开始

// 如果你使用php的依赖安装。可以使用以下方法自动载入
require 'vendor/autoload.php';
 
// 或者将你下载的medoo文件拷贝到你相应的目录,然后载入即可
require_once 'medoo.php';
 
// 初始化配置
$database = new medoo([
    'database_type' => 'mysql',
    'database_name' => 'name',
    'server' => 'localhost',
    'username' => 'your_username',
    'password' => 'your_password',
    'charset' => 'utf8'
]);
 
// 插入数据示例
$database->insert('account', [
    'user_name' => 'foo',
    'email' => 'foo@bar.com',
    'age' => 25,
    'lang' => ['en', 'fr', 'jp', 'cn']
]);

 

或者:

// Require Composer's autoloader.
require 'vendor/autoload.php';
 
// Using Medoo namespace.
use Medoo\Medoo;
 
// Connect the database.
$database = new Medoo([
    'type' => 'mysql',
    'host' => 'localhost',
    'database' => 'name',
    'username' => 'your_username',
    'password' => 'your_password'
]);
 
$database->insert('account', [
    'user_name' => 'foo',
    'email' => 'foo@bar.com'
]);
 
$data = $database->select('account', [
    'user_name',
    'email'
], [
    'user_id' => 50
]);
 
echo json_encode($data);
 
// [{
//     "user_name" : "foo",
//     "email" : "foo@bar.com"
// }]

 

Medoo各配置参数的含义

<?php
require 'Medoo.php';
use Medoo\Medoo;
 
$database = new Medoo([
	'type' => 'mysql',
	'host' => 'localhost',
	'database' => 'name',
	'username' => 'your_username',
	'password' => 'your_password',
	'charset' => 'utf8mb4',
	'collation' => 'utf8mb4_general_ci',
	'port' => 3306,
	'prefix' => 'PREFIX_',
	'logging' => true,
	'error' => PDO::ERRMODE_SILENT,
    'option' => [
		PDO::ATTR_CASE => PDO::CASE_NATURAL
	],
    'command' => [
		'SET SQL_MODE=ANSI_QUOTES'
	]
]);

 

从刚才的代码里可以看到,加载Medoo可以通过实例化一个类来完成。

$database = new Medoo([
	'type' => 'mysql',
	'host' => 'localhost',
	'database' => 'name',
	'username' => 'your_username',
	'password' => 'your_password',
......
]);

 

里面需要传递一个数组,数组的参数挺多的,不过大部分比较容易理解,这里面有必须填的,也有非必填的:

  1. type:你要连接的数据库类型,如mysqlmssql等;
  2. host:数据库IP或者主机名称;
  3. database:数据库库名;
  4. username:数据库账号;
  5. password:数据库密码;
  6. charset:数据库字符集,这个不设置会出现乱码;
  7. collation:数据库的字符排序规则;
  8. port:数据库的端口;
  9. prefix:数据库表前缀;
  10. logging:是否开启日志;
  11. error:捕获SQL错误的模式;
  12. option:设置PDO的模式;
  13. command:执行的SQL命令。

从字面意思可以看到,必填的一定有:typehostdatabaseusernamepasswordcharsetport,至于其它的是不是必填项,就需要你自己去摸索啦!

 

开始  (版本:2.1.4)

使用 Medoo 非常简单!

 

要求

  • PHP 7.3+ 已安装 PDO 扩展。
  • 已安装 SQL 数据库,如 MySQL、MSSQL、SQLite 或其他。
  • 确保正确安装并启用了 php_pdo_xxx 扩展。
  • 一点SQL知识。

 

PHP_PDO 扩展列表

以下列表根据数据库或系统平台的类型和版本指示应安装哪个 PDO 扩展。

Name Driver
MySQL, MariaDB php_pdo_mysql
MSSQL php_pdo_sqlsrv / php_pdo_dblib
Oracle php_pdo_oci
SQLite php_pdo_sqlite
PostgreSQL php_pdo_pgsql
Sybase php_pdo_dblib

 

PHP PDO 驱动安装

Medoo 需要支持 PDO 的 PHP。如果您之前没有安装过,请按照此步骤操作。

// 编辑 php.ini 文件并删除 ';' 对于要安装的数据库扩展。
 
// .dll 适用于 Windows,.so 适用于 Linux/UNIX。
 
// 从
;extension=php_pdo_mysql.dll
;extension=php_pdo_mysql.so
 
// 到
extension=php_pdo_mysql.dll
extension=php_pdo_mysql.so
 
// 保存,并重启 PHP 或 Apache/Nginx 服务器。
 
// 如果 PDO 安装成功,您可以在 phpinfo() 输出中找到它。

 

或者通过终端安装。PDO_XXX 扩展将被启用并自动配置。

$ sudo apt-get install php7.4-mysql

 

Composer Installation

如果您了解composer,则很容易安装和管理版本依赖。只需使用此命令将 Medoo 添加到 composer.json。或者您可以根据您的要求对其进行编辑。

$ composer require catfan/medoo

 

并更新

$ composer update

 

WHERE 语法

一些 Medoo 函数需要 $where 参数来过滤记录,如 SQL WHERE 子句,它功能强大但有很多复杂的语法、逻辑相关性和关于 SQL 注入的潜在安全问题。但是 Medoo 提供了一种强大且简单的方法来构建 WHERE 查询子句并防止注入。

 

基本情况

基本条件简单易懂。您可以使用附加符号来获取数字的高级筛选范围。

$database->select("account", "user_name", [
	"email" => "foo@bar.com"
]);
// WHERE email = 'foo@bar.com'
 
$database->select("account", "user_name", [
	"user_id" => 200
]);
// WHERE user_id = 200
 
$database->select("account", "user_name", [
	"user_id[>]" => 200
]);
// WHERE user_id > 200
 
$database->select("account", "user_name", [
	"user_id[>=]" => 200
]);
// WHERE user_id >= 200
 
$database->select("account", "user_name", [
	"user_id[!]" => 200
]);
// WHERE user_id != 200
 
$database->select("account", "user_name", [
	"age[<>]" => [200, 500]
]);
// WHERE age BETWEEN 200 AND 500
 
$database->select("account", "user_name", [
	"age[><]" => [200, 500]
]);
// WHERE age NOT BETWEEN 200 AND 500

 

[><]并且[<>]也可用于日期时间。

$database->select("account", "user_name", [
	"birthday[<>]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
 
$database->select("account", "user_name", [
	"birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);

 

WHERE ("birthday" BETWEEN '2015-01-01' AND '2017-01-01')
WHERE ("birthday" NOT BETWEEN '2015-01-01' AND '2017-01-01')

 

您不仅可以使用单个字符串或数字值,还可以使用数组

$database->select("account", "user_name", [
	"OR" => [
		"user_id" => [2, 123, 234, 54],
		"email" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"]
	]
]);
WHERE
user_id IN (2,123,234,54) OR
email IN ('foo@bar.com','cat@dog.com','admin@medoo.in')

 

Negative condition

$database->select("account", "user_name", [
	"AND" => [
		"user_name[!]" => "foo",
		"user_id[!]" => 1024,
		"email[!]" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"],
		"city[!]" => null,
		"promoted[!]" => true
	]
]);
WHERE
"user_name" != 'foo' AND
"user_id" != 1024 AND
"email" NOT IN ('foo@bar.com','cat@dog.com','admin@medoo.in') AND
"city" IS NOT NULL
"promoted" != 1

 

或取自select()get()函数。

$database->select("account", "user_name", [
	"user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
]);
WHERE user_id IN (2, 51, 321, 3431)

 

相对性条件

相对性条件可以描述数据与数据之间的复杂关系。您可以使用ANDOR来构建复杂的相对性条件查询。

 

基本的
$database->select("account", "user_name", [
	"AND" => [
		"user_id[>]" => 200,
		"age[<>]" => [18, 25],
		"gender" => "female"
	]
]);
 
// Medoo will connect the relativity condition with AND by default. The following usage is the same as above.
$database->select("account", "user_name", [
	"user_id[>]" => 200,
	"age[<>]" => [18, 25],
	"gender" => "female"
]);
WHERE user_id > 200 AND age BETWEEN 18 AND 25 AND gender = 'female'

 

$database->select("account", "user_name", [
	"OR" => [
		"user_id[>]" => 200,
		"age[<>]" => [18, 25],
		"gender" => "female"
	]
]);
WHERE user_id > 200 OR age BETWEEN 18 AND 25 OR gender = 'female'

 

Compound
database->has("account", [
	"AND" => [
		"OR" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"password" => "12345"
	]
]);
WHERE (user_name = 'foo' OR email = 'foo@bar.com') AND password = '12345'

 

由于 Medoo 使用数组数据构造来描述相关性条件,因此具有重复键的数组将被覆盖。

// This will be error:
$database->select("account", '*', [
	"AND" => [
		"OR" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"OR" => [
			"user_name" => "bar",
			"email" => "bar@foo.com"
		]
	]
]);
// [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = 'bar@foo.com')

 

要更正该问题,只需为每个 AND 和 OR 键名称(# 与空格)分配一个注释。评论内容可以是一切。

$database->select("account", '*', [
	"AND #Actually, this comment feature can be used on every AND and OR relativity condition" => [
		"OR #the first condition" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"OR #the second condition" => [
			"user_name" => "bar",
			"email" => "bar@foo.com"
		]
	]
]);
WHERE (
	("user_name" = 'foo' OR "email" = 'foo@bar.com')
	AND
	("user_name" = 'bar' OR "email" = 'bar@foo.com')
)

 

列关系
$database->select("post", [
	"[>]account" => "user_id",
], [
	"post.content"
], [
	// Connect two columns with condition signs like [=], [>], [<], [!=] as one of array value.
	"post.restrict[<]account.age"
]);
WHERE "post"."restrict" < "account"."age"

 

LIKE 条件

LIKE 条件可以像基本条件或相对条件一样使用,只需添加[~]语法。

// By default, the keyword will be quoted with % front and end to match the whole word.
$database->select("person", "id", [
	"city[~]" => "lon"
]);
WHERE "city" LIKE '%lon%'

 

Group
$database->select("person", "id", [
	"city[~]" => ["lon", "foo", "bar"]
]);
WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'

 

Negative condition
$database->select("person", "id", [
	"city[!~]" => "lon"
]);
WHERE "city" NOT LIKE '%lon%'

 

Compound
  1. $database->select(“person”, “id”, [
  2. “content[~]” => [“AND” => [“lon”, “on”]]
  3. ]);
  1. WHERE (“content” LIKE ‘%lon%’ AND “content” LIKE ‘%on%’)
  1. $database->select(“person”, “id”, [
  2. “content[~]” => [“OR” => [“lon”, “on”]]
  3. ]);
  1. WHERE (“content” LIKE ‘%lon%’ OR “content” LIKE ‘%on%’)
SQL Wildcard

You can use SQL wildcard to match more complex situation.

  1. $database->select(“person”, “id”, [
  2. “city[~]” => “%stan” // Kazakhstan, Uzbekistan, Türkmenistan
  3. ]);
  4.  
  5. $database->select(“person”, “id”, [
  6. “city[~]” => “Londo_” // London, Londox, Londos…
  7. ]);
  8.  
  9. $database->select(“person”, “id”, [
  10. “name[~]” => “[BCR]at” // Bat, Cat, Rat
  11. ]);
  12.  
  13. $database->select(“person”, “id”, [
  14. “name[~]” => “[!BCR]at” // Eat, Fat, Hat…
  15. ]);

Order Condition

  1. $database->select(“account”, “user_id”, [
  2. // Single condition.
  3. “ORDER” => “user_id”,
  4.  
  5. // Multiple condition.
  6. “ORDER” => [
  7. // Order by column with sorting by custom order.
  8. “user_id” => [43, 12, 57, 98, 144, 1],
  9.  
  10. // Order by column.
  11. “register_date”,
  12.  
  13. // Order by column with descending sorting.
  14. “profile_id” => “DESC”,
  15.  
  16. // Order by column with ascending sorting.
  17. “date” => “ASC”
  18. ]
  19. ]);

Full Text Searching

Full-text searching feature is supported by MySQL database for an advanced search result.

Search mode list
natural IN NATURAL LANGUAGE MODE
natural+query IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
boolean IN BOOLEAN MODE
query WITH QUERY EXPANSION
  1. // [MATCH]
  2. $database->select(“post_table”, “post_id”, [
  3. “MATCH” => [
  4. “columns” => [“content”, “title”],
  5. “keyword” => “foo”,
  6.  
  7. // [optional] Search mode.
  8. “mode” => “natural”
  9. ]
  10. ]);
  1. WHERE MATCH (content, title) AGAINST (‘foo’ IN NATURAL LANGUAGE MODE)

Using Regular Expression

  1. $data = $database->select(‘account’, [
  2. ‘user_id’,
  3. ‘user_name’
  4. ], [
  5. ‘user_name[REGEXP]’ => ‘[a-z0-9]*’
  6. ]);
  1. WHERE “user_name” REGEXP ‘[a-z0-9]*’

Using SQL Functions

You can now use SQL functions with the raw object for complex usage. Read more from https://medoo.in/api/raw.

  1. $data = $database->select(‘account’, [
  2. ‘user_id’,
  3. ‘user_name’
  4. ], [
  5. ‘datetime’ => Medoo::raw(‘NOW()’)
  6. ]);
  1. WHERE “datetime” = NOW()

LIMIT And OFFSET

  1. $database->select(“account”, “user_id”, [
  2. // Get the first 100 of rows.
  3. ‘LIMIT’ => 100
  4.  
  5. // Start from the top 20 rows and get the next 100.
  6. ‘LIMIT’ => [20, 100],
  7.  
  8. // For Oracle and MSSQL databases, you also need to use ORDER BY together.
  9. ‘ORDER’ => ‘location’
  10. ]);

GROUP And HAVING

  1. $database->select(“account”, “user_id”, [
  2. ‘GROUP’ => ‘type’,
  3.  
  4. // GROUP by array of values.
  5. ‘GROUP’ => [
  6. ‘type’,
  7. ‘age’,
  8. ‘gender’
  9. ],
  10.  
  11. ‘HAVING’ => [
  12. ‘user_id[>]’ => 500
  13. ]
  14. ]);

本文:轻量级 PHP 数据库框架, Medoo入门, Medoo使用指南, Medoo中文文档, Medoo数据库基本操作, Medoo安装和配置, Medoo的使用教程

Loading

Add a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.