ktyr report

CREATE TABLE `users` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARBINARY(100) NULL DEFAULT NULL,
	`email` VARBINARY(200) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id` (`id`)
)
ENGINE=InnoDB
;

crypto_saltは任意の文字列

SET @key_str = SHA2('crypto_salt',512);
INSERT INTO users(name,email) VALUES(AES_ENCRYPT('佐藤鈴木',@key_str),AES_ENCRYPT('satosuzuki@nullmail.com',@key_str));

SET @key_str = SHA2('crypto_salt',512);
SELECT id,AES_DECRYPT(`name`,@key_str) AS name,AES_DECRYPT(`email`,@key_str) AS email FROM users;

require_once 'idiorm.php';
ORM::configure('mysql:host=localhost;dbname=dev_db');
ORM::configure('username', 'root');
ORM::configure('password', '');
ORM::configure('driver_options', [
    PDO::MYSQL_ATTR_INIT_COMMAND       => 'SET NAMES utf8',
    PDO::ATTR_EMULATE_PREPARES         => false,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
]);
function echo_json($data)
{
    header("Content-Type: application/json; charset=utf-8");
    echo json_encode($data);
}
$words = mb_split('[\s,　]',addcslashes(trim($_POST['keywords']),'\_%'));
try{
    $orm = ORM::for_table('entries')->select('id')->where_equal('deleted','0')->order_by_asc('id');
    foreach ($words as $w) {
        $w = trim($w);
        if($w !== '')
            $orm->where_like('search', "%{$w}%");
    }
$recipes = $orm->find_many();
}catch (PDOException $e){
}
$result_data = array();
if(!empty($recipes)){
    foreach ($recipes as $recipe)
        $result_data[] = $recipe->html_id;
}
echo_json($result_data);
exit;

update wp_options set option_value='/manage' where option_name = 'siteurl';
update wp_options set option_value='/' where option_name = 'home';
update wp_options set option_value='/uploads' where option_name = 'upload_url_path';
update wp_options set option_value='../uploads' where option_name = 'upload_path';
update wp_options set option_value='' where option_name = 'blogdescription';
update wp_options set option_value='0' where option_name = 'start_of_week';
update wp_options set option_value='Y/m/d' where option_name = 'date_format';
update wp_options set option_value='H:i' where option_name = 'time_format';
update wp_options set option_value='closed' where option_name = 'default_comment_status';
update wp_options set option_value='closed' where option_name = 'default_ping_status';
update wp_options set option_value='0' where option_name = 'thumbnail_size_w';
update wp_options set option_value='0' where option_name = 'thumbnail_size_h';
update wp_options set option_value='0' where option_name = 'medium_size_w';
update wp_options set option_value='0' where option_name = 'medium_size_h';
update wp_options set option_value='0' where option_name = 'large_size_w';
update wp_options set option_value='0' where option_name = 'large_size_h';
update wp_options set option_value='0' where option_name = 'medium_large_size_w';
update wp_options set option_value='0' where option_name = 'medium_large_size_h';
update wp_options set option_value='0' where option_name = 'uploads_use_yearmonth_folders';
update wp_options set option_value='/%postname%/' where option_name = 'permalink_structure';

plugin require

Advanced Custom Fields PRO
Custom Post Type UI
Scheduled Post Trigger
User Role Editor
WP Term Order
Duplicate Post

plugin option

Radio Buttons for Taxonomies
Parent Category Toggler
Adjust Admin Categories

REGEXPはマルチバイトに対応していないので、FIND_IN_SETを使用。

INSERT INTO xxx(initial_chara,gyo)
(
SELECT
SUBSTRING(tmp.name,1,1),
(CASE
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'あ,い,う,え,お') THEN 'あ'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'か,き,く,け,こ,が,ぎ,ぐ,げ,ご') THEN 'か'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'さ,し,す,せ,そ,ざ,じ,ず,ぜ,ぞ') THEN 'さ'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'た,ち,つ,て,と,だ,ぢ,づ,で,ど') THEN 'た'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'な,に,ぬ,ね,の') THEN 'な'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'は,ひ,ふ,へ,ほ,ば,び,ぶ,べ,ぼ') THEN 'は'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'ま,み,む,め,も') THEN 'ま'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'や,ゐ,ゆ,ゑ,よ') THEN 'や'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'ら,り,る,れ,ろ') THEN 'ら'
WHEN FIND_IN_SET(SUBSTRING(tmp.name,1,1),'わ,を,ん') THEN 'わ'
ELSE 'ん' END
)
FROM yyy AS tmp

生年月日から年齢を算出する

■PHP

$birth = '1952-01-26 00:00:00';
$age = (int) ((date('Ymd') - preg_replace('/([\- ]|[0-9]{2}:[0-9]{2}:[0-9]{2})/','',$birth)) / 10000);
echo $age;

結果

65

■MySQL

new_users

SQL

SELECT
id,
name,
birth,
DATE_FORMAT(birth, '%Y%m%d') AS tmp_birth,
DATE_FORMAT(NOW(), '%Y%m%d') AS now_date,
FLOOR((DATE_FORMAT(NOW(), '%Y%m%d') - DATE_FORMAT(birth, '%Y%m%d')) / 10000) AS age
FROM new_users
ORDER BY id ASC

結果

開始位置は1からなので注意

all_test

SQL

SELECT id,name,tel,zip,SUBSTRING(zip,1,3) AS zip1,SUBSTRING(zip,4,4) AS zip2
FROM all_test
ORDER BY id ASC;

結果

下記2つのテーブルをtelカラムでjoinしたい

all_test

sub_infos

SQL

SELECT a.id,a.name,a.tel,s.id AS sub_id,s.tel AS sub_tel
FROM all_test AS a
LEFT JOIN sub_infos AS s
ON FIND_IN_SET(a.tel,REPLACE(s.tel,'-',''))
ORDER BY id ASC;

結果

検索結果ファイル出力

SELECT id,ken_id,ken_name FROM ad_address GROUP BY ken_name ORDER BY ken_id ASC LIMIT 55 INTO OUTFILE "tmp/test.csv" FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

XAMPP+コマンドプロンプト
MySQLのダンプファイルをリストアする時、文字コードを揃えないと文字化けする。
ダンプファイルがUTF-8の時

chcp 65001
mysql -u user -p < C:\xampp\htdocs\abc.dump

その後確認する時

chcp 932
mysql -u user -p ※MySQLログイン
set names sijis
select * from tablename;

mysqldump

mysqldump --default-character-set=utf8 -u ユーザ名 -p DB名 > /var/www/html/dump.sql