Database & SQL

環境

端末	環境／FW	最終更新
Windows	SQL Server 2022 Developer 16.0.4155.4	2024/11/13
	- SQL Server Management Studio 21.0.73 Preview 2.1	2025/01/05
	- SQL Server Management Studio 20.2.30	2024/08/24
	PostgreSQL 17.2	2024/12/31
	MongoDB 8.0.1	2024/10/19
	- Mongodump	2024/10/19
	MySQL Community Server 8.4.2	2024/08/24
InsiderDev	PostgreSQL 17.2	2024/12/31
	MySQL Community Server 9.1.0	2025/01/04

ノウハウ

データベース全般

• データベースを作成し、テーブルを追加する
• 大量データの作成、日付の加工

MongoDB

• インストール
  1. Scoopのインストール

     Set-ExecutionPolicy RemoteSigned -scope CurrentUser
     iex (new-object net.webclient).downloadstring('https://get.scoop.sh') 
     

  2. MongoDBのインストール

     scoop install mongodb
     

  3. MongoDB起動

     mongod
     

  4. 接続文字列

     mongodb://localhost:27017
     

  5. バックアップとリストア
     • バックアップは実行時のカレントディレクトリのdumpディレクトリに作成される
     • バックアップ

       mongodump
       

     • リストア

       mongorestore
       

  6. MongoDBのアップデート

     scoop update mongodb
     

     1. 8.0 への移行
     • 8.0 で互換性を保証するバージョンは、"7.0","7.3","8.0"であるため、"featureCompatibilityVersion"はそのいずれかにしておく必要あり

       {
         "_id": "featureCompatibilityVersion",
         "version": "7.0"
       }
       

       • 今回、7.1.1から移行する際、上述の"featureCompatibilityVersion"が"7.1"となっており、8.0を起動できなかった。
• MongoDB for VS Codeをインストールしておくとよい

SQL Server

• 接続文字列

  Server=localhost;Database=master;Trusted_Connection=True;
  

• データベース ユーザーの作成
  • admin/sqlsuper
• SSMS(SQL Server Management Studio)
  • アップデート後の初回起動時に旧バージョンから設定をインポートできる
  • SSMS 21.0.73 Preview 2.1
    • 64ビット化されたVisual Studioベースのため、Visual Studio Installer でインストール可能
    • コンポート名とバージョン情報

      SQL Server Management Studio						21.0.73.35617.110
      SQL Server 管理オブジェクト (SMO)						17.100.59.0+a94dad0182852f5525ad6535c8774dfc17d1a80f
      Microsoft T-SQL Parser						17.2.9.1+6d57751656f58b56aee52eb2638218ed0c2e70f8.6d57751656f58b56aee52eb2638218ed0c2e70f8
      Microsoft Data SqlClient (MDS)						5.1.6
      Microsoft SQL Server データ層アプリケーション フレームワーク (DacFX)						162.4.96.1+aa6267a9a33cd6e077fe8ea5fa178573b0bf53a1
      Microsoft .NET Framework						4.0.30319.42000
      オペレーティング システム						10.0.26100
      

  • SSMS 20.2.3
• エラー:18456 でログインできない場合
  • サーバ認証について、Windows認証モードとSQL Server認証モードが選択されているかを確認する
  • SQL Serverを再起動する
• 特定の位置にカラムを追加する

PostgreSQL

• PgAdmin対応表

  PostgreSQL	PgAdmin4
  17.2	8.14
  17.0	8.12
  16.4	8.10
  16.1	7.8
  16	7.6

• postgres/pgsuper
• リリース間移行
  • 並行稼働のため、新バージョンは現行バージョンとは異なるポート番号とする（現行：5432、新：5433）
  • 現行バージョンのバックアップ

    set PGCLIENTENCODING="UTF8"
    pg_dumpall -p 5432 --username=postgres > <BackupFile>
    

  • ロケールの編集（データベース作成部分）
    • バックアップされた内容例

      CREATE DATABASE batch WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'Japanese_Japan.932';
      

    • 編集後の内容例

      CREATE DATABASE batch WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'Japanese_Japan.932' LC_CTYPE = 'Japanese_Japan.932';
      

  • 新バージョンへのリストア

    set PGCLIENTENCODING="UTF8"
    type <Edited_BackupFile> | psql -d postgres -p 5433 --username=postgres
    

• タイムアウト時間の設定（マシン性能の問題等で、PgAdminを起動できない場合の対応）
  • サーバとの接続がタイムアウトしているため、タイムアウト時間を延ばす
• コマンドラインでのSQL
  • psqlにPATHが設定されていないので、追加する必要あり
• 参照専用ユーザの作成
  • データを更新することはなく、参照だけできれば良い場合 運用時のユーザや開発者向けユーザとは別に、参照専用のユーザがあると有効
  • PostgreSQL 14以降の場合、データの参照向けロールがあるので、そのロールを設定すればよい。

    >grant pg_read_all_data to <User>
    

• PHP + Apache + PostgreSQL

MySQL

• Dumpをロードするには、local_infileをONにしておく必要あり

  SET GLOBAL local_infile = ON;
  

• MySQL Community Server 8.4.2 (LTS)

  • Oracleプロファイル作成のうえ、ダウンロード
  • インストールすると、MySQL Configuratorで設定する root/mysqlsuper
  Configulation Log

  Beginning configuration step: Writing configuration file
  
  Saving my.ini configuration file...
  Saved my.ini configuration file.
  Ended configuration step: Writing configuration file
  
  Beginning configuration step: Updating Windows Firewall rules
  
  Adding a Windows Firewall rule for MySQL84 on port 3306.
  Attempting to add a Windows Firewall rule with command: netsh.exe advfirewall firewall add rule name="Port 3306" protocol=TCP localport=3306 dir=in action=allow
  Ok.
  
  
  Successfully added the Windows Firewall rule.
  Adding a Windows Firewall rule for MySQL84 on port 33060.
  Attempting to add a Windows Firewall rule with command: netsh.exe advfirewall firewall add rule name="Port 33060" protocol=TCP localport=33060 dir=in action=allow
  Ok.
  
  
  Successfully added the Windows Firewall rule.
  Ended configuration step: Updating Windows Firewall rules
  
  Beginning configuration step: Adjusting Windows service
  
  Attempting to grant the required filesystem permissions to the 'NT AUTHORITY\NetworkService' account.
  Granted permissions to the data directory.
  Granted permissions to the install directory.
  Adding new service
  New service added
  Ended configuration step: Adjusting Windows service
  
  Beginning configuration step: Initializing database (may take a long time)
  
  Attempting to run MySQL Server with --initialize-insecure option...
  Starting process for MySQL Server 8.4.2...
  Starting process with command: C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.4\my.ini" --console --initialize-insecure=on --lower-case-table-names=1...
  MySQL Server Initialization - start.
  C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld.exe (mysqld 8.4.2) initializing of server in progress as process 15752
  InnoDB initialization has started.
  InnoDB initialization has ended.
  root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
  MySQL Server Initialization - end.
  Process for mysqld, with ID 15752, was run successfully and exited with code 0.
  Successfully started process for MySQL Server 8.4.2.
  MySQL Server 8.4.2 intialized the database successfully.
  Ended configuration step: Initializing database (may take a long time)
  
  Beginning configuration step: Updating permissions for the data folder and related server files
  
  Attempting to update the permissions for the data folder and related server files...
  Inherited permissions have been converted to explicit permissions.
  Full control permissions granted to: NETWORK SERVICE.
  Full control permissions granted to: Administrators.
  Full control permissions granted to: CREATOR OWNER.
  Full control permissions granted to: SYSTEM.
  Access to the data directory is removed for the users group.
  Permissions for the data folder and related server files are updated correctly.
  Ended configuration step: Updating permissions for the data folder and related server files
  
  Beginning configuration step: Starting the server
  
  Attempting to start service MySQL84...
  MySQL Server - start.
  C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld.exe (mysqld 8.4.2) starting as process 13844
  InnoDB initialization has started.
  InnoDB initialization has ended.
  CA certificate ca.pem is self signed.
  Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
  X Plugin ready for connections. Bind-address: '::' port: 33060
  C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld.exe: ready for connections. Version: '8.4.2'  socket: ''  port: 3306  MySQL Community Server - GPL.
  Successfully started service MySQL84.
  Waiting until a connection to MySQL Server 8.4.2 can be established (with a maximum of 10 attempts)...
  Retry 1: Attempting to connect to Mysql@localhost:3306 with user root with no password...
  Successfully connected to MySQL Server 8.4.2.
  Ended configuration step: Starting the server
  
  Beginning configuration step: Applying security settings
  
  Attempting to update security settings.
  Updated security settings.
  Ended configuration step: Applying security settings
  
  Beginning configuration step: Updating the Start menu link
  
  Attempting to verify command-line client shortcut.
  Verified command-line client shortcut.
  Verified command-line client shortcut.
  Ended configuration step: Updating the Start menu link
  
  Beginning configuration step: Updating example databases
  
  Updating example databases...
  Ended configuration step: Updating example databases
  

• MySQL Shell for VS Code

  • VSCodeユーザ向けにもユーティリティがある！
  • 簡単にテーブルのデータを確認できる

• MySQL 8.0.28

  • ZIPファイルを任意のディレクトリに展開する
  • my.iniを展開したディレクトリに作成する

    [mysqld]
    # set basedir to your installation path
    basedir=D:\mysql-8.0.28-winx64
    # set datadir to the location of your data directory
    datadir=D:\mysql-8.0.28-winx64\data
    

  • データディレクトリを初期化する（パスワード生成せず）

    mysqld.exe --initialize-insecure --console
    

  • MySQL Server を起動する（パスワードなし）

    mysql.exe -u root --skip-password
    

  • MySQLをサービスに登録する

    D:\mysql-8.0.28-winx64\bin\mysqld --install MYSQL80
    

  • MySQLのサービスを開始する

    net start MYSQL80
    

  • DDL
    • rootパスワードの変更

      mysql> ALTER USER root@localhost IDENTIFIED BY "root";
      

    • ユーザの追加およびパスワードの登録

      mysql> CREATE USER taish;
      mysql> ALTER USER taish@localhost IDENTIFIED BY "taishow";