colOrder = $order; } public static function desc($table) { return 'DESC ' . $table; } public static function truncate($table) { return 'TRUNCATE TABLE `' . $table . '`'; } public static function opt($table) { return 'OPTIMIZE TABLE `' . $table . '`'; } public static function explain($sql) { return 'EXPLAIN ' . $sql; } public static function showIndex($table) { return 'SHOW INDEX FROM ' . $table; } public static function create($config) { $sql = 'DROP TABLE IF EXISTS `' . $config['table'] . '`;CREATE TABLE IF NOT EXISTS `' . $config['table'] . '`('; $struct = array('id' => array('name' => 'ID', 'type' => 'int(11)'),'cdate' => array('name' => 'cdate', 'type' => 'int(11)')); $struct = array_merge($struct, $config['struct']); foreach ($struct as $k => $v) { $sql .= self::createField($k, $v) . ','; } $sql = rtrim($sql, ',') . ')'; if (isset($config['auto'])) { $sql .= ' AUTO_INCREMENT = ' . $config['auto']; } if (isset($config['type'])) { $sql .= ' ENGINE = ' . $config['type'] . ';'; } return $sql . ' COMMENT="'.$config['name'].'"'; } public static function createField($name, $set) { $field = '`' . $name . '` ' . strtoupper($set['type']); if ($name == 'id') { $field .= 'UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL'; } elseif (isset($set['default']) && $set['default']) { $field .= ' NOT NULL DEFAULT "' . $set['default'] . '"'; } elseif (strpos($set['type'], 'int') !== false || strpos($set['type'], 'float') !== false || strpos($set['type'], 'decimal') !== false || strpos($set['type'], 'double') !== false) { $field .= ' NOT NULL DEFAULT 0'; } else { $field .= ' NULL'; } if (isset($set['name'])) { $field .= ' COMMENT \'' . $set['name'] . '\''; } return $field; } public static function alter($table, $struct, $data) { $sql = array(); $alter = 'ALTER TABLE `' . $table . '` '; foreach ($data as $v) { $field = $v['Field']; if ($field != 'id') { $set = $struct[$field] ?? false; if ($set) { if ($set['type'] != $v['Type'] || (isset($set['default']) && $set['default'] != $v['Default'])) { $sql[] = $alter . ' CHANGE `' . $field . '` `' . $field . '` ' . self::createField($field, $set); } else { unset($struct[$field]); } } else { $sql[] = $alter . ' DROP `' . $field . '`'; } } } if ($struct) { foreach ($struct as $k => $v) { $sql[] = $alter . ' ADD ' . self::createField($k, $v); } } return implode(';', $sql); } public static function index($table, $index, $del = array()) { $sql = array(); $alter = 'ALTER TABLE `' . $table . '` '; foreach ($del as $v) { if ($v['Key_name'] != 'PRIMARY') { $sql[] = $alter . ' DROP INDEX ' . $v['Key_name']; } } if ($index) { foreach ($index as $k => $v) { $t = 'INDEX'; if (strpos($v, '.')) { list($v, $t) = explode('.', $v); } $sql[] = $alter . ' ADD ' . strtoupper($t) . ' ' . $k . ' (' . $v . ')'; } } return implode(';', $sql); } public static function partition($table, $partition, $index) { $state = true; foreach ($index as $k => $v) { if ($v['Key_name'] == 'PRIMARY' && $v['Column_name'] == 'cdate') { $state = false; } } $sql = ''; if ($state) { return 'ALTER TABLE `' . $table . '` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `cdate`) USING BTREE;ALTER TABLE `' . $table . '` PARTITION BY RANGE (cdate) (PARTITION p'.$partition.' VALUES LESS THAN ('.$partition.'));'; } else { return 'ALTER TABLE `' . $table . '` ADD PARTITION (PARTITION p'.$partition.' VALUES LESS THAN ('.$partition.'));'; } } public static function select($table, $param, &$bind, $set = array(), $field = array()) { $col = '*'; $rule = ''; if (isset($set['col'])) { $col = $set['col']; } if (isset($set['join'])) { $temp = explode('_', $table); $prefix = $temp[0] . '_' . $temp[1] . '_'; $table .= ' AS ' . $temp[2]; foreach ($set['join'] as $k => $v) { $table .= ' ' . $v['type'] . ' ' . $prefix . $v['table'] . ' AS ' . $v['table'] . ' ON ' . $v['on']; } } if (isset($set['group'])) { $rule .= ' GROUP BY ' . $set['group']; } if (isset($set['order'])) { $rule .= ' ORDER BY ' . $set['order']; } if (isset($set['limit'])) { if (is_array($set['limit'])) { $table .= ' inner join (select id from ' . $table . self::where($param, $bind, $field) . $rule . ' limit ' . $set['limit'][0].','.$set['limit'][1].') as t on '.$table.'.id=t.id'; $rule = ''; $param = false; } else { $rule .= ' LIMIT ' . $set['limit']; } } return 'SELECT ' . $col . ' FROM ' . $table . self::where($param, $bind, $field) . $rule; } public static function where($param, &$bind, $field = array()) { if ($param) { $first = $second = ''; if (is_array($param)) { $i = 0; foreach ($param as $k => $v) { if (strpos($k, '#')) { $k = trim($k, '#'); } if ($k == 'or' || $k == 'and') { $first_link = $second_link = ''; foreach ($v as $k1 => $v1) { if (is_array($v1)) { self::field($second_link, $bind, $i, $k1, $v1[0], $v1[1], $field); } else { self::field($first_link, $bind, $i, $k1, '=', $v1, $field); } } $first_link = ltrim($first_link, ' and '); $second .= ' ' . $k . ' (' . $first_link . $second_link . ')'; } else { if (is_array($v)) { self::field($second, $bind, $i, $k, $v[0], $v[1], $field); } else { self::field($first, $bind, $i, $k, '=', $v, $field); } } } return ' WHERE ' . ltrim($first . $second, ' and '); } elseif (is_numeric($param)) { return ' WHERE id = ' . $param; } else { return ' WHERE ' . $param; } } else { return ''; } } private static function field(&$s, &$b, &$i, $k, $e, $v, $f) { if ($f && empty($f[$k]) && strpos('id,cdate', $k) === false) { return; } $s .= ' and '; $p = ':'.$k.$i; $k = '`'.$k.'`'; if (strpos($e,'in') !== false) { $s .= $k.$e.'('.$p.')'; } elseif ($e == 'like') { $s .= 'instr('.$k.','.$p.')'.' > 0'; } elseif ($e == 'group') { $v = ','.$v.','; $s .= 'instr(concat(",",'.$k.',","),'.$p.')' . ' > 0'; } elseif ($e == 'between') { $b[$p.'_e'] = $v[1]; $v = $v[0]; $s .= $k.' between '.$p.' and '.$p.'_e'; } else { $s .= $k.$e.$p; } $b[$p] = $v; $i++; } public static function insert($table, $data, &$bind, $field) { $sql = 'INSERT INTO `' . $table . '` SET ';#IGNORE foreach ($data as $k => $v) { if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) { continue; } $sql .= '`' . $k . '`=:' . $k . ','; $bind[':'.$k] = $v; } $sql = rtrim($sql, ','); return $sql; } public static function update($table, $param, $data, &$bind, $field) { $i = 0; $sql = 'UPDATE `' . $table . '` SET '; foreach ($data as $k => $v) { if ($field && empty($field[$k]) && strpos('id,cdate', $k) === false) { continue; } $a = ''; if (is_array($v)) { $a = $v[1]; $v = $v[0]; } $sql .= '`' . $k . '`=`' . $k . '`' . $a . ':' . $k . ','; $bind[':'.$k] = $v; } return rtrim($sql, ',') . self::where($param, $bind, $field); } public static function delete($table, $param, &$bind, $field) { return 'DELETE FROM `' . $table . '`' . self::where($param, $bind, $field); } public static function inserts($table, $param) { $num = $param['num'] ?? 1; $sql = 'INSERT INTO `' . $table . '` (' . $param['field'] . ') VALUES '; foreach ($param['value'] as $k => $v) { for ($i = 1; $i <= $num; $i++) { $insert[] = '(' . $v . ')'; } } $sql .= implode(',', $insert) . ','; return rtrim($sql, ','); } }